-- PostgreSQL Database Schema
-- Generated by MCP Backend Generator

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create updated_at function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for categories
CREATE INDEX IF NOT EXISTS idx_categories_name ON categories(name);
CREATE INDEX IF NOT EXISTS idx_categories_parent_id ON categories(parent_id);

-- Create trigger for categories updated_at
CREATE TRIGGER update_categories_updated_at
    BEFORE UPDATE ON categories
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    sku VARCHAR(100) UNIQUE NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category_id UUID NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for products
CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);
CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id);
CREATE INDEX IF NOT EXISTS idx_products_price ON products(price);
CREATE INDEX IF NOT EXISTS idx_products_stock_quantity ON products(stock_quantity);

-- Create trigger for products updated_at
CREATE TRIGGER update_products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Inventory movements table
CREATE TABLE IF NOT EXISTS inventory_movements (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    movement_type VARCHAR(20) NOT NULL CHECK (movement_type IN ('IN', 'OUT', 'ADJUSTMENT')),
    quantity INTEGER NOT NULL,
    reference VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for inventory_movements
CREATE INDEX IF NOT EXISTS idx_inventory_movements_product_id ON inventory_movements(product_id);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_type ON inventory_movements(movement_type);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_created_at ON inventory_movements(created_at);

-- Insert sample data
INSERT INTO categories (name, description) VALUES 
    ('Electronics', 'Electronic devices and accessories'),
    ('Clothing', 'Apparel and fashion items'),
    ('Books', 'Books and educational materials')
ON CONFLICT (name) DO NOTHING;

-- Insert subcategories
INSERT INTO categories (name, description, parent_id) VALUES 
    ('Computers', 'Desktop and laptop computers', (SELECT id FROM categories WHERE name = 'Electronics')),
    ('Smartphones', 'Mobile phones and accessories', (SELECT id FROM categories WHERE name = 'Electronics')),
    ('Men''s Clothing', 'Clothing for men', (SELECT id FROM categories WHERE name = 'Clothing')),
    ('Women''s Clothing', 'Clothing for women', (SELECT id FROM categories WHERE name = 'Clothing'))
ON CONFLICT (name) DO NOTHING;

-- Insert sample products
INSERT INTO products (name, description, sku, price, category_id, stock_quantity) VALUES 
    ('Gaming Laptop', 'High-performance gaming laptop with RTX graphics', 'LAPTOP-001', 1299.99, 
     (SELECT id FROM categories WHERE name = 'Computers'), 10),
    ('Smartphone Pro', 'Latest smartphone with advanced camera', 'PHONE-001', 899.99, 
     (SELECT id FROM categories WHERE name = 'Smartphones'), 25),
    ('Cotton T-Shirt', 'Comfortable cotton t-shirt in various colors', 'TSHIRT-001', 19.99, 
     (SELECT id FROM categories WHERE name = 'Men''s Clothing'), 50),
    ('Programming Book', 'Learn modern web development', 'BOOK-001', 39.99, 
     (SELECT id FROM categories WHERE name = 'Books'), 30)
ON CONFLICT (sku) DO NOTHING;

-- Insert initial inventory movements
INSERT INTO inventory_movements (product_id, movement_type, quantity, reference, notes)
SELECT 
    p.id,
    'IN',
    p.stock_quantity,
    'INITIAL_STOCK',
    'Initial stock entry'
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM inventory_movements im 
    WHERE im.product_id = p.id AND im.reference = 'INITIAL_STOCK'
);

-- Create views for common queries
CREATE OR REPLACE VIEW product_inventory AS
SELECT 
    p.id,
    p.name,
    p.sku,
    p.price,
    c.name as category_name,
    p.stock_quantity,
    COALESCE(stock_in.total_in, 0) as total_stock_in,
    COALESCE(stock_out.total_out, 0) as total_stock_out,
    p.is_active
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
    SELECT 
        product_id, 
        SUM(quantity) as total_in
    FROM inventory_movements 
    WHERE movement_type = 'IN'
    GROUP BY product_id
) stock_in ON p.id = stock_in.product_id
LEFT JOIN (
    SELECT 
        product_id, 
        SUM(quantity) as total_out
    FROM inventory_movements 
    WHERE movement_type = 'OUT'
    GROUP BY product_id
) stock_out ON p.id = stock_out.product_id;

-- Create function to update product stock
CREATE OR REPLACE FUNCTION update_product_stock()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        IF NEW.movement_type = 'IN' THEN
            UPDATE products 
            SET stock_quantity = stock_quantity + NEW.quantity
            WHERE id = NEW.product_id;
        ELSIF NEW.movement_type = 'OUT' THEN
            UPDATE products 
            SET stock_quantity = stock_quantity - NEW.quantity
            WHERE id = NEW.product_id;
        ELSIF NEW.movement_type = 'ADJUSTMENT' THEN
            UPDATE products 
            SET stock_quantity = stock_quantity + NEW.quantity
            WHERE id = NEW.product_id;
        END IF;
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to automatically update stock
DROP TRIGGER IF EXISTS trigger_update_product_stock ON inventory_movements;
CREATE TRIGGER trigger_update_product_stock
    AFTER INSERT ON inventory_movements
    FOR EACH ROW
    EXECUTE FUNCTION update_product_stock();

-- Create function for low stock alerts
CREATE OR REPLACE FUNCTION get_low_stock_products(threshold INTEGER DEFAULT 10)
RETURNS TABLE(
    product_id UUID,
    product_name VARCHAR(200),
    sku VARCHAR(100),
    current_stock INTEGER,
    category_name VARCHAR(100)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.id,
        p.name,
        p.sku,
        p.stock_quantity,
        c.name
    FROM products p
    JOIN categories c ON p.category_id = c.id
    WHERE p.stock_quantity <= threshold
      AND p.is_active = true
    ORDER BY p.stock_quantity ASC;
END;
$$ LANGUAGE plpgsql;

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_products_active_stock ON products(is_active, stock_quantity);
CREATE INDEX IF NOT EXISTS idx_categories_active ON categories(is_active);

-- Grant permissions (adjust as needed)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

COMMIT;