-- Sales & Distribution Module Database Schema

-- 1. Sales Orders Table
CREATE TABLE sales_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    status ENUM('draft', 'confirmed', 'in_progress', 'shipped', 'delivered', 'cancelled', 'returned') DEFAULT 'draft',
    total_amount DECIMAL(15,2) DEFAULT 0,
    tax_amount DECIMAL(15,2) DEFAULT 0,
    discount DECIMAL(15,2) DEFAULT 0,
    net_amount DECIMAL(15,2) DEFAULT 0,
    delivery_address TEXT,
    delivery_date DATE,
    shipping_method VARCHAR(50) DEFAULT 'standard',
    shipping_cost DECIMAL(15,2) DEFAULT 0,
    payment_status ENUM('pending', 'partial', 'paid', 'overdue', 'cancelled') DEFAULT 'pending',
    notes TEXT,
    created_by INT,
    approved_by INT,
    approved_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,
    cancelled_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    FOREIGN KEY (cancelled_by) REFERENCES users(id)
);

-- 2. Sales Order Items Table
CREATE TABLE sales_order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sales_order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(15,2) NOT NULL,
    total_price DECIMAL(15,2) NOT NULL,
    delivered_quantity DECIMAL(10,2) DEFAULT 0,
    batch_number VARCHAR(100), -- For tracking specific product batches
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 3. Pricing Tables
CREATE TABLE pricing_tables (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NULL, -- NULL for general pricing
    price_type ENUM('list', 'wholesale', 'promotional', 'customer_specific', 'volume_discount') DEFAULT 'list',
    price DECIMAL(15,2) NOT NULL,
    effective_from DATE NOT NULL,
    effective_to DATE NULL,
    min_quantity DECIMAL(10,2) DEFAULT 1, -- For volume discounts
    max_quantity DECIMAL(10,2) NULL,      -- For volume discounts
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 4. Delivery Orders Table
CREATE TABLE delivery_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    delivery_number VARCHAR(50) UNIQUE NOT NULL,
    sales_order_id INT NOT NULL,
    delivery_date DATE NOT NULL,
    driver_id INT, -- User ID of the driver
    vehicle_number VARCHAR(20),
    shipping_address TEXT,
    status ENUM('pending', 'in_transit', 'delivered', 'failed', 'returned') DEFAULT 'pending',
    delivery_fee DECIMAL(15,2) DEFAULT 0,
    special_instructions TEXT,
    delivered_at TIMESTAMP NULL,
    delivered_by INT, -- User ID of person who recorded the delivery
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id),
    FOREIGN KEY (driver_id) REFERENCES users(id),
    FOREIGN KEY (delivered_by) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 5. Delivery Order Items Table
CREATE TABLE delivery_order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    delivery_order_id INT NOT NULL,
    sales_order_item_id INT NOT NULL,
    quantity_to_deliver DECIMAL(10,2) NOT NULL,
    batch_number VARCHAR(100), -- To track which batch is being delivered
    delivered_quantity DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (delivery_order_id) REFERENCES delivery_orders(id),
    FOREIGN KEY (sales_order_item_id) REFERENCES sales_order_items(id)
);

-- 6. Sales Returns Table
CREATE TABLE sales_returns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    return_number VARCHAR(50) UNIQUE NOT NULL,
    sales_order_id INT NOT NULL,
    customer_id INT NOT NULL,
    reason TEXT,
    status ENUM('pending', 'approved', 'rejected', 'processed') DEFAULT 'pending',
    total_refund DECIMAL(15,2) DEFAULT 0,
    notes TEXT,
    approved_by INT,
    approved_at TIMESTAMP NULL,
    processed_by INT,
    processed_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    FOREIGN KEY (processed_by) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 7. Sales Return Items Table
CREATE TABLE sales_return_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sales_return_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(15,2) NOT NULL,
    total_amount DECIMAL(15,2) NOT NULL,
    reason TEXT,
    batch_number VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sales_return_id) REFERENCES sales_returns(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 8. Customer Orders Summary (for performance)
CREATE TABLE customer_order_summary (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL,
    total_orders INT DEFAULT 0,
    total_amount DECIMAL(15,2) DEFAULT 0,
    total_quantity DECIMAL(10,2) DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    UNIQUE KEY unique_customer_month (customer_id, year, month)
);

-- Create indexes for better performance
CREATE INDEX idx_sales_orders_status ON sales_orders(status);
CREATE INDEX idx_sales_orders_customer ON sales_orders(customer_id);
CREATE INDEX idx_sales_orders_date ON sales_orders(created_at);
CREATE INDEX idx_delivery_orders_status ON delivery_orders(status);
CREATE INDEX idx_delivery_orders_date ON delivery_orders(delivery_date);
CREATE INDEX idx_sales_returns_status ON sales_returns(status);
CREATE INDEX idx_pricing_product ON pricing_tables(product_id);
CREATE INDEX idx_pricing_customer ON pricing_tables(customer_id);

-- Insert sample data
INSERT INTO sales_orders (order_number, customer_id, status, total_amount, net_amount, delivery_date, created_by) VALUES
('SO2023001', 1, 'confirmed', 2500000, 2500000, DATE_ADD(CURDATE(), INTERVAL 2 DAY), 1);

INSERT INTO sales_order_items (sales_order_id, product_id, quantity, unit_price, total_price) VALUES
(1, 1, 10, 150000, 1500000),
(1, 2, 5, 200000, 1000000);