CREATE TABLE users (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(255),
    role VARCHAR(50) DEFAULT 'viewer' CHECK (role IN ('admin', 'manager', 'employee', 'viewer')),
    avatar_url TEXT,
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMPTZ,
    preferences JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_active ON users(is_active);

CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    ip_address INET,
    user_agent TEXT,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    parent_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    
    -- Pricing
    cost_price DECIMAL(10,2) DEFAULT 0,
    selling_price DECIMAL(10,2) DEFAULT 0,
    msrp DECIMAL(10,2),
    
    -- Physical attributes
    weight DECIMAL(8,3),
    length DECIMAL(8,2),
    width DECIMAL(8,2),
    height DECIMAL(8,2),
    volume DECIMAL(10,3),
    
    -- Inventory settings
    reorder_point INTEGER DEFAULT 0,
    max_stock_level INTEGER,
    min_order_quantity INTEGER DEFAULT 1,
    
    -- Product status
    is_active BOOLEAN DEFAULT true,
    is_serialized BOOLEAN DEFAULT false,
    track_expiry BOOLEAN DEFAULT false,
    
    -- Metadata
    tags JSONB DEFAULT '[]',
    custom_fields JSONB DEFAULT '{}',
    
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(is_active);
CREATE INDEX idx_products_tags ON products USING GIN(tags);

CREATE TABLE product_barcodes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    barcode VARCHAR(255) NOT NULL,
    barcode_type VARCHAR(50) DEFAULT 'UPC-A',
    is_primary BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE product_variants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    variant_name VARCHAR(255) NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    
    -- Variant-specific pricing
    cost_price DECIMAL(10,2),
    selling_price DECIMAL(10,2),
    
    -- Variant attributes
    attributes JSONB DEFAULT '{}', -- {"size": "L", "color": "red"}
    
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE warehouses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL,
    
    -- Address information
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'US',
    
    -- Contact information
    phone VARCHAR(20),
    email VARCHAR(255),
    manager_id UUID REFERENCES users(id) ON DELETE SET NULL,
    
    -- Capacity settings
    capacity_limit INTEGER,
    capacity_unit VARCHAR(20) DEFAULT 'units',
    
    -- Operational settings
    timezone VARCHAR(50) DEFAULT 'UTC',
    operating_hours JSONB DEFAULT '{}',
    
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE warehouse_zones (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) NOT NULL,
    zone_type VARCHAR(50) DEFAULT 'storage', -- storage, staging, shipping, receiving
    capacity INTEGER,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(warehouse_id, code)
);

CREATE TABLE warehouse_locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
    zone_id UUID REFERENCES warehouse_zones(id) ON DELETE SET NULL,
    location_code VARCHAR(100) NOT NULL,
    
    -- Location hierarchy: Zone-Aisle-Shelf-Bin
    aisle VARCHAR(10),
    shelf VARCHAR(10),
    bin VARCHAR(10),
    
    -- Capacity
    max_capacity INTEGER,
    current_capacity INTEGER DEFAULT 0,
    
    -- Location attributes
    temperature_controlled BOOLEAN DEFAULT false,
    hazmat_approved BOOLEAN DEFAULT false,
    
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(warehouse_id, location_code)
);

CREATE TABLE inventory (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
    location_id UUID REFERENCES warehouse_locations(id) ON DELETE SET NULL,
    
    -- Quantity tracking
    quantity_on_hand INTEGER DEFAULT 0,
    quantity_allocated INTEGER DEFAULT 0,
    quantity_available INTEGER GENERATED ALWAYS AS (quantity_on_hand - quantity_allocated) STORED,
    
    -- Cost tracking
    average_cost DECIMAL(10,2) DEFAULT 0,
    last_cost DECIMAL(10,2) DEFAULT 0,
    
    -- Audit fields
    last_counted_at TIMESTAMPTZ,
    last_movement_at TIMESTAMPTZ,
    
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(product_id, warehouse_id)
);

CREATE INDEX idx_inventory_product ON inventory(product_id);
CREATE INDEX idx_inventory_warehouse ON inventory(warehouse_id);
CREATE INDEX idx_inventory_available ON inventory(quantity_available);
CREATE INDEX idx_inventory_low_stock ON inventory(quantity_on_hand, quantity_allocated) 
    WHERE quantity_on_hand <= quantity_allocated;

CREATE TABLE stock_movements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
    
    -- Movement details
    movement_type VARCHAR(50) NOT NULL CHECK (movement_type IN ('in', 'out', 'adjustment', 'transfer')),
    quantity INTEGER NOT NULL,
    unit_cost DECIMAL(10,2),
    
    -- Balance tracking
    balance_before INTEGER NOT NULL,
    balance_after INTEGER NOT NULL,
    
    -- Reference information
    reference_type VARCHAR(50), -- purchase_order, sales_order, adjustment, transfer
    reference_id UUID,
    
    -- Additional details
    notes TEXT,
    performed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Partition by month for performance
CREATE TABLE stock_movements_y2024m01 PARTITION OF stock_movements
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE inventory_adjustments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    adjustment_number VARCHAR(100) UNIQUE NOT NULL,
    warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
    
    -- Adjustment details
    adjustment_type VARCHAR(50) NOT NULL, -- physical_count, damage, loss, found
    reason TEXT,
    
    -- Status and workflow
    status VARCHAR(50) DEFAULT 'draft' CHECK (status IN ('draft', 'approved', 'applied')),
    approved_by UUID REFERENCES users(id) ON DELETE SET NULL,
    approved_at TIMESTAMPTZ,
    
    -- Audit
    created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE inventory_adjustment_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    adjustment_id UUID NOT NULL REFERENCES inventory_adjustments(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    
    -- Quantity differences
    expected_quantity INTEGER NOT NULL,
    actual_quantity INTEGER NOT NULL,
    difference_quantity INTEGER GENERATED ALWAYS AS (actual_quantity - expected_quantity) STORED,
    
    -- Cost impact
    unit_cost DECIMAL(10,2),
    cost_impact DECIMAL(10,2) GENERATED ALWAYS AS (difference_quantity * unit_cost) STORED,
    
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE suppliers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) UNIQUE,
    
    -- Contact information
    email VARCHAR(255),
    phone VARCHAR(20),
    website VARCHAR(255),
    
    -- Address
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'US',
    
    -- Business information
    tax_id VARCHAR(50),
    payment_terms VARCHAR(50) DEFAULT 'net_30',
    credit_limit DECIMAL(12,2),
    
    -- Primary contact
    contact_name VARCHAR(255),
    contact_email VARCHAR(255),
    contact_phone VARCHAR(20),
    
    -- Performance metrics
    performance_rating DECIMAL(3,2) DEFAULT 0,
    on_time_delivery_rate DECIMAL(5,2) DEFAULT 0,
    quality_rating DECIMAL(3,2) DEFAULT 0,
    
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE purchase_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    po_number VARCHAR(100) UNIQUE NOT NULL,
    supplier_id UUID NOT NULL REFERENCES suppliers(id) ON DELETE CASCADE,
    warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
    
    -- Order details
    order_date DATE DEFAULT CURRENT_DATE,
    expected_date DATE,
    delivery_date DATE,
    
    -- Financial
    subtotal DECIMAL(12,2) DEFAULT 0,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    tax_amount DECIMAL(12,2) DEFAULT 0,
    shipping_cost DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0,
    
    -- Status workflow
    status VARCHAR(50) DEFAULT 'draft' 
        CHECK (status IN ('draft', 'sent', 'confirmed', 'partial', 'received', 'cancelled')),
    
    -- Additional information
    notes TEXT,
    terms TEXT,
    internal_notes TEXT,
    
    -- Audit
    created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    approved_by UUID REFERENCES users(id) ON DELETE SET NULL,
    approved_at TIMESTAMPTZ,
    
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE purchase_order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_order_id UUID NOT NULL REFERENCES purchase_orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    
    -- Order quantities
    quantity_ordered INTEGER NOT NULL,
    quantity_received INTEGER DEFAULT 0,
    quantity_cancelled INTEGER DEFAULT 0,
    quantity_pending INTEGER GENERATED ALWAYS AS (quantity_ordered - quantity_received - quantity_cancelled) STORED,
    
    -- Pricing
    unit_cost DECIMAL(10,2) NOT NULL,
    total_cost DECIMAL(12,2) GENERATED ALWAYS AS (quantity_ordered * unit_cost) STORED,
    
    -- Delivery
    expected_date DATE,
    
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    customer_number VARCHAR(100) UNIQUE,
    
    -- Contact information
    email VARCHAR(255),
    phone VARCHAR(20),
    website VARCHAR(255),
    
    -- Billing address
    billing_address_line1 VARCHAR(255),
    billing_address_line2 VARCHAR(255),
    billing_city VARCHAR(100),
    billing_state VARCHAR(100),
    billing_postal_code VARCHAR(20),
    billing_country VARCHAR(100) DEFAULT 'US',
    
    -- Shipping address (if different)
    shipping_address_line1 VARCHAR(255),
    shipping_address_line2 VARCHAR(255),
    shipping_city VARCHAR(100),
    shipping_state VARCHAR(100),
    shipping_postal_code VARCHAR(20),
    shipping_country VARCHAR(100) DEFAULT 'US',
    
    -- Business information
    tax_id VARCHAR(50),
    payment_terms VARCHAR(50) DEFAULT 'due_on_receipt',
    credit_limit DECIMAL(12,2) DEFAULT 0,
    current_balance DECIMAL(12,2) DEFAULT 0,
    
    -- Customer preferences
    preferred_shipping_method VARCHAR(100),
    discount_rate DECIMAL(5,2) DEFAULT 0,
    
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE sales_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_number VARCHAR(100) UNIQUE NOT NULL,
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
    
    -- Order details
    order_date DATE DEFAULT CURRENT_DATE,
    requested_ship_date DATE,
    promised_ship_date DATE,
    actual_ship_date DATE,
    
    -- Financial
    subtotal DECIMAL(12,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    tax_amount DECIMAL(12,2) DEFAULT 0,
    shipping_cost DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0,
    
    -- Status workflow
    status VARCHAR(50) DEFAULT 'draft' 
        CHECK (status IN ('draft', 'confirmed', 'picking', 'packed', 'shipped', 'delivered', 'cancelled')),
    
    -- Shipping information
    shipping_method VARCHAR(100),
    tracking_number VARCHAR(255),
    
    -- Additional information
    notes TEXT,
    internal_notes TEXT,
    
    -- Audit
    created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE sales_order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sales_order_id UUID NOT NULL REFERENCES sales_orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    
    -- Order quantities
    quantity_ordered INTEGER NOT NULL,
    quantity_shipped INTEGER DEFAULT 0,
    quantity_cancelled INTEGER DEFAULT 0,
    quantity_pending INTEGER GENERATED ALWAYS AS (quantity_ordered - quantity_shipped - quantity_cancelled) STORED,
    
    -- Pricing
    unit_price DECIMAL(10,2) NOT NULL,
    discount_rate DECIMAL(5,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(12,2) GENERATED ALWAYS AS ((unit_price * quantity_ordered) - discount_amount) STORED,
    
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    sales_order_id UUID REFERENCES sales_orders(id) ON DELETE SET NULL,
    
    -- Invoice details
    invoice_date DATE DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,
    
    -- Financial
    subtotal DECIMAL(12,2) NOT NULL,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    tax_amount DECIMAL(12,2) DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    amount_paid DECIMAL(12,2) DEFAULT 0,
    balance_due DECIMAL(12,2) GENERATED ALWAYS AS (total_amount - amount_paid) STORED,
    
    -- Status
    status VARCHAR(50) DEFAULT 'draft' 
        CHECK (status IN ('draft', 'sent', 'paid', 'overdue', 'cancelled')),
    
    -- Payment information
    payment_terms VARCHAR(100),
    payment_method VARCHAR(50),
    
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE expenses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supplier_id UUID REFERENCES suppliers(id) ON DELETE SET NULL,
    
    -- Expense details
    expense_number VARCHAR(100) UNIQUE,
    description TEXT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    expense_date DATE DEFAULT CURRENT_DATE,
    
    -- Classification
    category VARCHAR(100),
    subcategory VARCHAR(100),
    
    -- Payment
    payment_method VARCHAR(50),
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
    
    -- Supporting documentation
    receipt_url TEXT,
    receipt_file_name VARCHAR(255),
    
    -- Tax information
    tax_amount DECIMAL(10,2) DEFAULT 0,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    
    notes TEXT,
    created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name VARCHAR(100) NOT NULL,
    record_id UUID NOT NULL,
    action VARCHAR(50) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    
    -- Change tracking
    old_values JSONB,
    new_values JSONB,
    changed_fields TEXT[],
    
    -- Context
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    session_id UUID,
    ip_address INET,
    user_agent TEXT,
    
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Partition audit logs by month
CREATE TABLE audit_logs_y2024m01 PARTITION OF audit_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

erDiagram
    users ||--o{ products : creates
    users ||--o{ purchase_orders : creates
    users ||--o{ sales_orders : creates
    
    categories ||--o{ products : contains
    products ||--o{ product_barcodes : has
    products ||--o{ product_variants : has
    products ||--o{ inventory : tracked_in
    products ||--o{ stock_movements : moves
    
    warehouses ||--o{ inventory : stores
    warehouses ||--o{ stock_movements : records
    warehouses ||--o{ warehouse_zones : contains
    warehouse_zones ||--o{ warehouse_locations : contains
    
    suppliers ||--o{ purchase_orders : receives
    purchase_orders ||--o{ purchase_order_items : contains
    
    customers ||--o{ sales_orders : places
    customers ||--o{ invoices : billed_to
    sales_orders ||--o{ sales_order_items : contains
    sales_orders ||--o{ invoices : generates
    
    products ||--o{ purchase_order_items : ordered
    products ||--o{ sales_order_items : sold

graph TD
    A[Purchase Order] --> B[Stock Movement IN]
    B --> C[Inventory Update]
    D[Sales Order] --> E[Stock Movement OUT]
    E --> C
    F[Adjustment] --> G[Stock Movement ADJUSTMENT]
    G --> C
    H[Transfer] --> I[Stock Movement TRANSFER]
    I --> C

-- Product search and filtering
CREATE INDEX idx_products_name_gin ON products USING GIN(to_tsvector('english', name));
CREATE INDEX idx_products_description_gin ON products USING GIN(to_tsvector('english', description));

-- Inventory queries
CREATE INDEX idx_inventory_low_stock ON inventory 
    WHERE quantity_on_hand <= (
        SELECT reorder_point FROM products WHERE products.id = inventory.product_id
    );

-- Order management
CREATE INDEX idx_purchase_orders_status_date ON purchase_orders(status, order_date);
CREATE INDEX idx_sales_orders_customer_date ON sales_orders(customer_id, order_date);

-- Analytics and reporting
CREATE INDEX idx_stock_movements_date ON stock_movements(created_at);
CREATE INDEX idx_stock_movements_product_date ON stock_movements(product_id, created_at);

-- Audit and compliance
CREATE INDEX idx_audit_logs_table_record ON audit_logs(table_name, record_id);
CREATE INDEX idx_audit_logs_user_date ON audit_logs(user_id, created_at);

-- Multi-column indexes for common query patterns
CREATE INDEX idx_inventory_product_warehouse ON inventory(product_id, warehouse_id);
CREATE INDEX idx_stock_movements_product_warehouse_date ON stock_movements(product_id, warehouse_id, created_at);
CREATE INDEX idx_purchase_order_items_order_product ON purchase_order_items(purchase_order_id, product_id);

-- Enable RLS on all tables
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase_orders ENABLE ROW LEVEL SECURITY;
-- ... enable for all tables

-- Admin access - full access to all data
CREATE POLICY admin_all_access ON products
    FOR ALL TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM users 
            WHERE users.id = auth.uid() 
            AND users.role = 'admin'
        )
    );

-- Manager access - department-specific data
CREATE POLICY manager_products_access ON products
    FOR ALL TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM users 
            WHERE users.id = auth.uid() 
            AND users.role IN ('admin', 'manager')
        )
    );

-- Employee access - read and limited write
CREATE POLICY employee_products_read ON products
    FOR SELECT TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM users 
            WHERE users.id = auth.uid() 
            AND users.role IN ('admin', 'manager', 'employee')
        )
    );

-- Viewer access - read only
CREATE POLICY viewer_products_read ON products
    FOR SELECT TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM users 
            WHERE users.id = auth.uid()
        )
    );

-- Warehouse-based access control
CREATE POLICY warehouse_inventory_access ON inventory
    FOR ALL TO authenticated
    USING (
        warehouse_id IN (
            SELECT warehouse_id FROM user_warehouse_access 
            WHERE user_id = auth.uid()
        )
        OR EXISTS (
            SELECT 1 FROM users 
            WHERE users.id = auth.uid() 
            AND users.role IN ('admin', 'manager')
        )
    );

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_logs (
            table_name, record_id, action, old_values, user_id, created_at
        ) VALUES (
            TG_TABLE_NAME, OLD.id, TG_OP, row_to_json(OLD), auth.uid(), now()
        );
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_logs (
            table_name, record_id, action, old_values, new_values, user_id, created_at
        ) VALUES (
            TG_TABLE_NAME, NEW.id, TG_OP, row_to_json(OLD), row_to_json(NEW), auth.uid(), now()
        );
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_logs (
            table_name, record_id, action, new_values, user_id, created_at
        ) VALUES (
            TG_TABLE_NAME, NEW.id, TG_OP, row_to_json(NEW), auth.uid(), now()
        );
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Apply audit trigger to tables
CREATE TRIGGER products_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON products
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER inventory_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON inventory
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

CREATE OR REPLACE FUNCTION update_inventory_on_movement()
RETURNS TRIGGER AS $$
BEGIN
    -- Update inventory quantities based on stock movement
    IF NEW.movement_type = 'in' THEN
        UPDATE inventory 
        SET 
            quantity_on_hand = quantity_on_hand + NEW.quantity,
            last_movement_at = now(),
            updated_at = now()
        WHERE product_id = NEW.product_id 
        AND warehouse_id = NEW.warehouse_id;
        
    ELSIF NEW.movement_type = 'out' THEN
        UPDATE inventory 
        SET 
            quantity_on_hand = quantity_on_hand - NEW.quantity,
            last_movement_at = now(),
            updated_at = now()
        WHERE product_id = NEW.product_id 
        AND warehouse_id = NEW.warehouse_id;
        
    ELSIF NEW.movement_type = 'adjustment' THEN
        UPDATE inventory 
        SET 
            quantity_on_hand = NEW.balance_after,
            last_movement_at = now(),
            updated_at = now()
        WHERE product_id = NEW.product_id 
        AND warehouse_id = NEW.warehouse_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER stock_movement_inventory_update
    AFTER INSERT ON stock_movements
    FOR EACH ROW EXECUTE FUNCTION update_inventory_on_movement();

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at columns
CREATE TRIGGER update_products_updated_at 
    BEFORE UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_inventory_updated_at 
    BEFORE UPDATE ON inventory
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Product performance summary
CREATE MATERIALIZED VIEW product_performance AS
SELECT 
    p.id,
    p.name,
    p.sku,
    p.category_id,
    c.name AS category_name,
    SUM(i.quantity_on_hand) AS total_stock,
    AVG(i.average_cost) AS avg_cost,
    COUNT(DISTINCT i.warehouse_id) AS warehouses_count,
    SUM(CASE WHEN sm.movement_type = 'out' THEN sm.quantity ELSE 0 END) AS total_sold,
    MAX(sm.created_at) AS last_movement_date
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN inventory i ON p.id = i.product_id
LEFT JOIN stock_movements sm ON p.id = sm.product_id
WHERE p.is_active = true
GROUP BY p.id, p.name, p.sku, p.category_id, c.name;

CREATE UNIQUE INDEX idx_product_performance_id ON product_performance(id);

-- Low stock items view
CREATE VIEW low_stock_items AS
SELECT 
    p.id,
    p.name,
    p.sku,
    p.reorder_point,
    i.quantity_on_hand,
    i.quantity_allocated,
    i.quantity_available,
    w.name AS warehouse_name,
    CASE 
        WHEN i.quantity_on_hand = 0 THEN 'out_of_stock'
        WHEN i.quantity_available <= p.reorder_point THEN 'low_stock'
        ELSE 'in_stock'
    END AS stock_status
FROM products p
JOIN inventory i ON p.id = i.product_id
JOIN warehouses w ON i.warehouse_id = w.id
WHERE p.is_active = true
  AND (i.quantity_on_hand = 0 OR i.quantity_available <= p.reorder_point);

-- Order fulfillment view
CREATE VIEW order_fulfillment_status AS
SELECT 
    so.id,
    so.order_number,
    so.status,
    so.order_date,
    so.promised_ship_date,
    c.name AS customer_name,
    COUNT(soi.id) AS total_items,
    SUM(soi.quantity_ordered) AS total_quantity,
    SUM(soi.quantity_shipped) AS shipped_quantity,
    CASE 
        WHEN SUM(soi.quantity_shipped) = 0 THEN 'not_started'
        WHEN SUM(soi.quantity_shipped) < SUM(soi.quantity_ordered) THEN 'partial'
        ELSE 'complete'
    END AS fulfillment_status
FROM sales_orders so
JOIN customers c ON so.customer_id = c.id
JOIN sales_order_items soi ON so.id = soi.sales_order_id
GROUP BY so.id, so.order_number, so.status, so.order_date, so.promised_ship_date, c.name;

-- Enumerated types for consistent data
CREATE TYPE user_role AS ENUM ('admin', 'manager', 'employee', 'viewer');
CREATE TYPE order_status AS ENUM ('draft', 'confirmed', 'picking', 'packed', 'shipped', 'delivered', 'cancelled');
CREATE TYPE movement_type AS ENUM ('in', 'out', 'adjustment', 'transfer');
CREATE TYPE payment_terms AS ENUM ('due_on_receipt', 'net_15', 'net_30', 'net_45', 'net_60', 'net_90');

-- Composite types for structured data
CREATE TYPE address AS (
    line1 VARCHAR(255),
    line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100)
);

-- Domain types for validation
CREATE DOMAIN email AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN phone AS VARCHAR(20)
    CHECK (VALUE ~ '^\+?[1-9]\d{1,14}$');

CREATE DOMAIN money AS DECIMAL(12,2)
    CHECK (VALUE >= 0);

CREATE DOMAIN percentage AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);

-- migrations/001_initial_schema.sql
BEGIN;

-- Create extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create custom types
CREATE TYPE user_role AS ENUM ('admin', 'manager', 'employee', 'viewer');
-- ... other types

-- Create tables in dependency order
CREATE TABLE users (...);
CREATE TABLE categories (...);
CREATE TABLE products (...);
-- ... other tables

-- Create indexes
CREATE INDEX idx_products_sku ON products(sku);
-- ... other indexes

-- Enable RLS and create policies
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
-- ... RLS policies

-- Create functions and triggers
CREATE OR REPLACE FUNCTION audit_trigger_function() ...;
-- ... other functions

-- Create triggers
CREATE TRIGGER products_audit_trigger ...;
-- ... other triggers

COMMIT;

-- migrations/XXX_migration_name.sql
BEGIN;

-- Add your migration here
ALTER TABLE products ADD COLUMN new_field VARCHAR(100);
CREATE INDEX idx_products_new_field ON products(new_field);

-- Update existing data if needed
UPDATE products SET new_field = 'default_value' WHERE new_field IS NULL;

-- Add constraints
ALTER TABLE products ALTER COLUMN new_field SET NOT NULL;

COMMIT;

-- rollbacks/XXX_rollback_migration_name.sql
BEGIN;

-- Reverse the migration changes
ALTER TABLE products DROP CONSTRAINT IF EXISTS constraint_name;
DROP INDEX IF EXISTS idx_products_new_field;
ALTER TABLE products DROP COLUMN IF EXISTS new_field;

COMMIT;