CREATE TABLE e_commerce_products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT UNSIGNED DEFAULT 0,
    weight_kg FLOAT,
    is_featured TINYINT(1) DEFAULT 0,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (product_name),
    INDEX idx_featured (is_featured)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE e_commerce_products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10,2) NOT NULL,
    stock_quantity INTEGER CHECK (stock_quantity >= 0),
    weight_kg REAL,
    is_featured BOOLEAN DEFAULT FALSE,
    metadata JSONB,
    tags TEXT[],
    price_history NUMERIC(10,2)[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_price CHECK (price > 0)
);

CREATE INDEX idx_name ON e_commerce_products(product_name);
CREATE INDEX idx_featured ON e_commerce_products(is_featured) WHERE is_featured = TRUE;
CREATE INDEX idx_metadata ON e_commerce_products USING GIN (metadata);

-- On Windows, these are all equivalent:
SELECT * FROM Users;
SELECT * FROM users;
SELECT * FROM USERS;

-- Table names follow filesystem case sensitivity on Unix/Linux
-- Column names are always case-insensitive

SELECT Name, EMAIL, age FROM customers;
-- Works regardless of actual column name casing

-- PostgreSQL folds unquoted identifiers to lowercase
SELECT * FROM Users;     -- Looks for table "users"
SELECT * FROM "Users";   -- Looks for table "Users" exactly

-- Column names are also case-sensitive with quotes
SELECT name, email FROM customers;        -- Works
SELECT Name, Email FROM customers;        -- Works (folded to lowercase)
SELECT "Name", "Email" FROM customers;    -- Fails unless columns are exactly "Name" and "Email"

-- Case-insensitive by default (depends on collation)
SELECT * FROM users WHERE username = 'JOHN';
SELECT * FROM users WHERE username = 'john';
-- Both find 'John', 'john', 'JOHN', etc.

-- Pattern matching
SELECT * FROM products WHERE name LIKE 'widget%';  -- Case-insensitive
SELECT * FROM products WHERE name REGEXP '^[A-Z]'; -- Regular expressions

-- Case-sensitive by default
SELECT * FROM users WHERE username = 'JOHN';  -- Only finds exactly 'JOHN'
SELECT * FROM users WHERE username = 'john';  -- Only finds exactly 'john'

-- Case-insensitive comparison requires explicit handling
SELECT * FROM users WHERE LOWER(username) = LOWER('JOHN');
SELECT * FROM users WHERE username ILIKE 'john';  -- Case-insensitive LIKE

-- Pattern matching with multiple options
SELECT * FROM products WHERE name LIKE 'widget%';       -- Case-sensitive
SELECT * FROM products WHERE name ILIKE 'widget%';      -- Case-insensitive
SELECT * FROM products WHERE name ~ '^Widget';          -- Regex case-sensitive
SELECT * FROM products WHERE name ~* '^widget';         -- Regex case-insensitive
SELECT * FROM products WHERE name SIMILAR TO 'W(idget|are)%';  -- SQL pattern matching

-- Basic pagination
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 40;

-- MySQL also supports this shorter syntax
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 40, 20;  -- OFFSET, LIMIT (note the order!)

-- Standard SQL syntax
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 40;

-- PostgreSQL also supports FETCH (SQL standard)
SELECT * FROM orders 
ORDER BY created_at DESC 
OFFSET 40 ROWS 
FETCH FIRST 20 ROWS ONLY;

-- Method 1: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO user_preferences (user_id, theme, language, notifications)
VALUES (123, 'dark', 'en', true)
ON DUPLICATE KEY UPDATE
    theme = VALUES(theme),
    language = VALUES(language),
    notifications = VALUES(notifications),
    updated_at = CURRENT_TIMESTAMP;

-- Method 2: REPLACE (deletes and inserts)
REPLACE INTO user_preferences (user_id, theme, language, notifications)
VALUES (123, 'dark', 'en', true);

-- Method 3: INSERT IGNORE (skips if exists)
INSERT IGNORE INTO user_sessions (user_id, session_token, expires_at)
VALUES (123, 'abc123xyz', '2024-12-31 23:59:59');

-- INSERT ... ON CONFLICT (more powerful and standard)
INSERT INTO user_preferences (user_id, theme, language, notifications)
VALUES (123, 'dark', 'en', true)
ON CONFLICT (user_id) 
DO UPDATE SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language,
    notifications = EXCLUDED.notifications,
    updated_at = CURRENT_TIMESTAMP;

-- Can specify which conflict triggers the update
INSERT INTO user_emails (user_id, email, verified)
VALUES (123, 'user@example.com', false)
ON CONFLICT (email) 
DO UPDATE SET
    user_id = EXCLUDED.user_id,
    updated_at = CURRENT_TIMESTAMP
WHERE user_emails.verified = false;  -- Only update if not verified

-- DO NOTHING option
INSERT INTO audit_log (user_id, action, timestamp)
VALUES (123, 'login', CURRENT_TIMESTAMP)
ON CONFLICT (user_id, action, timestamp) 
DO NOTHING;

-- Create table with JSON
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    profile_data JSON
);

-- Insert JSON data
INSERT INTO user_profiles (user_id, profile_data)
VALUES (1, '{"name": "Zhang Wei", "age": 28, "city": "Beijing", "hobbies": ["reading", "coding"]}');

-- Query JSON data
SELECT 
    user_id,
    JSON_EXTRACT(profile_data, '$.name') AS name,
    JSON_EXTRACT(profile_data, '$.age') AS age,
    JSON_EXTRACT(profile_data, '$.hobbies[0]') AS first_hobby
FROM user_profiles;

-- Update JSON fields
UPDATE user_profiles
SET profile_data = JSON_SET(profile_data, '$.age', 29, '$.city', 'Shanghai')
WHERE user_id = 1;

-- Search within JSON
SELECT * FROM user_profiles
WHERE JSON_CONTAINS(profile_data, '"coding"', '$.hobbies');

-- Create indexes on JSON (MySQL 8.0+)
ALTER TABLE user_profiles 
ADD COLUMN city VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(profile_data, '$.city'))) STORED,
ADD INDEX idx_city (city);

-- Create table with JSONB (binary JSON, more efficient)
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY,
    profile_data JSONB
);

-- Insert JSON data
INSERT INTO user_profiles (user_id, profile_data)
VALUES (1, '{"name": "Zhang Wei", "age": 28, "city": "Beijing", "hobbies": ["reading", "coding"]}');

-- Query JSON data (multiple operator options)
SELECT 
    user_id,
    profile_data->>'name' AS name,                    -- Get as text
    (profile_data->>'age')::INTEGER AS age,           -- Get and cast
    profile_data->'hobbies'->0 AS first_hobby,        -- Get as JSON
    profile_data->'hobbies'->>0 AS first_hobby_text   -- Get as text
FROM user_profiles;

-- Path-based extraction
SELECT 
    user_id,
    profile_data #>> '{hobbies, 0}' AS first_hobby
FROM user_profiles;

-- Update JSON fields
UPDATE user_profiles
SET profile_data = jsonb_set(
    jsonb_set(profile_data, '{age}', '29'),
    '{city}', '"Shanghai"'
)
WHERE user_id = 1;

-- More elegant update with multiple fields
UPDATE user_profiles
SET profile_data = profile_data || '{"age": 29, "city": "Shanghai"}'::jsonb
WHERE user_id = 1;

-- Powerful querying operators
SELECT * FROM user_profiles
WHERE profile_data @> '{"city": "Beijing"}';  -- Contains

SELECT * FROM user_profiles
WHERE profile_data ? 'age';  -- Has key

SELECT * FROM user_profiles
WHERE profile_data ?| array['city', 'country'];  -- Has any of these keys

SELECT * FROM user_profiles
WHERE profile_data ?& array['name', 'age'];  -- Has all of these keys

SELECT * FROM user_profiles
WHERE profile_data -> 'hobbies' @> '"coding"';  -- Array contains value

-- Efficient JSONB indexing
CREATE INDEX idx_profile_data ON user_profiles USING GIN (profile_data);
CREATE INDEX idx_city ON user_profiles ((profile_data->>'city'));
CREATE INDEX idx_hobbies ON user_profiles USING GIN ((profile_data->'hobbies'));

-- Create table with full-text search
CREATE TABLE articles (
    article_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX idx_content (title, content)
) ENGINE=InnoDB;

-- Full-text search queries
SELECT 
    article_id, 
    title,
    MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- Boolean mode search (AND, OR, NOT operations)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+database +optimization -MySQL' IN BOOLEAN MODE);

-- Phrase search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"database optimization"' IN NATURAL LANGUAGE MODE);

-- Query expansion (finds related terms)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

-- Create table with text search
CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    title_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', title)) STORED,
    content_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
);

-- Create GIN indexes for fast searching
CREATE INDEX idx_title_tsv ON articles USING GIN (title_tsv);
CREATE INDEX idx_content_tsv ON articles USING GIN (content_tsv);

-- Basic full-text search
SELECT 
    article_id,
    title,
    ts_rank(content_tsv, to_tsquery('english', 'database & optimization')) AS rank
FROM articles
WHERE content_tsv @@ to_tsquery('english', 'database & optimization')
ORDER BY rank DESC;

-- Phrase search
SELECT * FROM articles
WHERE content_tsv @@ phraseto_tsquery('english', 'database optimization');

-- OR search
SELECT * FROM articles
WHERE content_tsv @@ to_tsquery('english', 'database | PostgreSQL | MySQL');

-- NOT search
SELECT * FROM articles
WHERE content_tsv @@ to_tsquery('english', 'database & !MySQL');

-- Search with headlines (highlighted excerpts)
SELECT 
    article_id,
    title,
    ts_headline('english', content, to_tsquery('english', 'database & optimization'),
                'MaxWords=50, MinWords=30, ShortWord=3') AS excerpt
FROM articles
WHERE content_tsv @@ to_tsquery('english', 'database & optimization');

-- Combined search across multiple fields
SELECT 
    article_id,
    title,
    ts_rank_cd(title_tsv || content_tsv, query) AS rank
FROM articles, to_tsquery('english', 'database & optimization') query
WHERE (title_tsv || content_tsv) @@ query
ORDER BY rank DESC;

-- Weighted search (title more important than content)
SELECT 
    article_id,
    title,
    ts_rank_cd(
        setweight(title_tsv, 'A') || setweight(content_tsv, 'B'),
        to_tsquery('english', 'database')
    ) AS rank
FROM articles
WHERE (title_tsv || content_tsv) @@ to_tsquery('english', 'database')
ORDER BY rank DESC;

-- Basic window functions (MySQL 8.0+)
SELECT 
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

-- Cumulative calculations
SELECT 
    order_date,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_date) AS running_total,
    AVG(order_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7days
FROM orders;

-- LAG and LEAD functions
SELECT 
    order_date,
    order_amount,
    LAG(order_amount, 1) OVER (ORDER BY order_date) AS previous_order,
    LEAD(order_amount, 1) OVER (ORDER BY order_date) AS next_order,
    order_amount - LAG(order_amount, 1) OVER (ORDER BY order_date) AS change_from_previous
FROM orders;

-- All MySQL window functions, plus additional capabilities
SELECT 
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER w AS row_num,
    RANK() OVER w AS salary_rank,
    PERCENT_RANK() OVER w AS percentile_rank,
    CUME_DIST() OVER w AS cumulative_dist,
    NTILE(4) OVER w AS quartile,
    FIRST_VALUE(employee_name) OVER w AS highest_paid,
    LAST_VALUE(employee_name) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_paid,
    NTH_VALUE(employee_name, 2) OVER w AS second_highest_paid
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

-- Advanced frame specifications
SELECT 
    order_date,
    order_amount,
    -- Different frame types
    AVG(order_amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS moving_avg_rows,
    AVG(order_amount) OVER (
        ORDER BY order_date 
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS moving_avg_range,
    -- Statistical functions
    STDDEV(order_amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS rolling_stddev_30days
FROM orders;

-- Filter clause with aggregations
SELECT 
    department,
    COUNT(*) AS total_employees,
    COUNT(*) FILTER (WHERE salary > 50000) AS high_earners,
    AVG(salary) AS avg_salary,
    AVG(salary) FILTER (WHERE hire_date > '2020-01-01') AS avg_salary_recent_hires
FROM employees
GROUP BY department;

-- Standard B-tree indexes
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_dept ON employees(last_name, department);

-- Full-text indexes
CREATE FULLTEXT INDEX idx_description ON products(description);

-- Spatial indexes (for geometry data)
CREATE SPATIAL INDEX idx_location ON stores(coordinates);

-- Index hints (force MySQL to use specific index)
SELECT * FROM users USE INDEX (idx_email)
WHERE email = 'user@example.com';

-- View index usage
SHOW INDEX FROM users;

-- Analyze query performance
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123;

-- B-tree indexes (default)
CREATE INDEX idx_email ON users USING btree (email);
CREATE INDEX idx_name_dept ON employees(last_name, department);

-- Partial indexes (PostgreSQL unique feature)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
CREATE INDEX idx_recent_orders ON orders(order_date) 
WHERE order_date > '2024-01-01';

-- Expression indexes
CREATE INDEX idx_lower_email ON users(LOWER(email));
CREATE INDEX idx_year_month ON orders(EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date));

-- GIN indexes (for JSON, arrays, full-text)
CREATE INDEX idx_tags ON products USING GIN (tags);
CREATE INDEX idx_metadata ON products USING GIN (metadata jsonb_path_ops);

-- GiST indexes (for geometric data, ranges)
CREATE INDEX idx_location ON stores USING gist (coordinates);
CREATE INDEX idx_date_range ON events USING gist (date_range);

-- BRIN indexes (for very large tables with natural ordering)
CREATE INDEX idx_created_at ON logs USING brin (created_at);

-- Concurrent index creation (doesn't block writes)
CREATE INDEX CONCURRENTLY idx_user_status ON users(status);

-- View index usage statistics
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'users';

-- Detailed query analysis
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM users WHERE email = 'user@example.com';

-- Optimize JOIN operations
SELECT 
    u.user_id,
    u.username,
    o.order_id,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE u.active = 1 
AND o.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY o.order_date DESC;

-- Use covering indexes (index contains all needed columns)
CREATE INDEX idx_covering ON orders(user_id, order_date, total_amount);

-- Optimize subqueries with EXISTS
SELECT u.*
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.user_id 
    AND o.status = 'completed'
);

-- Use UNION ALL instead of UNION when duplicates are acceptable
SELECT product_id, 'category_A' AS source FROM category_a_products
UNION ALL
SELECT product_id, 'category_B' AS source FROM category_b_products;

-- Same optimizations plus PostgreSQL-specific features
SELECT 
    u.user_id,
    u.username,
    o.order_id,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE u.active = true
AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY o.order_date DESC;

-- Lateral joins (correlated subqueries in FROM clause)
SELECT 
    u.user_id,
    u.username,
    recent.order_id,
    recent.total_amount
FROM users u
CROSS JOIN LATERAL (
    SELECT order_id, total_amount
    FROM orders o
    WHERE o.user_id = u.user_id
    ORDER BY order_date DESC
    LIMIT 5
) AS recent;

-- Common Table Expressions (CTEs) with optimization
WITH high_value_customers AS (
    SELECT user_id, SUM(total_amount) AS total_spent
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY user_id
    HAVING SUM(total_amount) > 10000
)
SELECT 
    u.username,
    u.email,
    hvc.total_spent
FROM users u
INNER JOIN high_value_customers hvc ON u.user_id = hvc.user_id;

-- Parallel query execution (PostgreSQL automatically uses for large datasets)
SET max_parallel_workers_per_gather = 4;
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department;