-- Supply Chain Module Database Schema

-- 1. Purchase Orders Table
CREATE TABLE purchase_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    po_number VARCHAR(50) UNIQUE NOT NULL,
    supplier_id INT NOT NULL,
    status ENUM('draft', 'pending_approval', 'approved', 'partially_received', 'received', 'closed', 'cancelled') 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,
    expected_date DATE,
    received_date DATE,
    notes TEXT,
    created_by INT,
    approved_by INT,
    approved_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)
);

-- 2. Purchase Order Items Table
CREATE TABLE purchase_order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    po_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,
    received_quantity DECIMAL(10,2) DEFAULT 0,
    delivered_quantity DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 3. Inventory Transactions Table
CREATE TABLE inventory_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_type ENUM('in', 'out', 'adjustment', 'production_in', 'production_out', 'damage', 'expired') NOT NULL,
    reference_type ENUM('po', 'so', 'production', 'adjustment', 'return', 'transfer') NOT NULL,
    reference_id INT NOT NULL,
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(15,2) DEFAULT 0,
    total_value DECIMAL(15,2) DEFAULT 0,
    batch_number VARCHAR(100),
    expiry_date DATE,
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 4. Stock Opname Table
CREATE TABLE stock_opname (
    id INT PRIMARY KEY AUTO_INCREMENT,
    opname_number VARCHAR(50) UNIQUE NOT NULL,
    warehouse_id INT NOT NULL,
    period DATE NOT NULL, -- Usually month-end date
    status ENUM('draft', 'in_progress', 'completed', 'cancelled') DEFAULT 'draft',
    created_by INT,
    completed_by INT,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (completed_by) REFERENCES users(id)
);

-- 5. Stock Opname Items Table
CREATE TABLE stock_opname_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    opname_id INT NOT NULL,
    product_id INT NOT NULL,
    book_quantity DECIMAL(10,2) DEFAULT 0,  -- Quantity in system
    actual_quantity DECIMAL(10,2) DEFAULT 0, -- Quantity counted
    difference DECIMAL(10,2) DEFAULT 0,      -- actual - book
    variance_notes TEXT,
    checked_by INT,
    checked_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (opname_id) REFERENCES stock_opname(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (checked_by) REFERENCES users(id)
);

-- 6. Product Batches Table (for expiry tracking)
CREATE TABLE product_batches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    batch_number VARCHAR(100) NOT NULL,
    expiry_date DATE,
    manufactured_date DATE,
    quantity DECIMAL(10,2) DEFAULT 0,
    cost_price DECIMAL(15,2) DEFAULT 0,
    supplier_id INT,
    po_id INT,
    received_date DATE,
    status ENUM('active', 'expired', 'disposed') DEFAULT 'active',
    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 (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id),
    UNIQUE KEY unique_product_batch (product_id, batch_number)
);

-- 7. Supplier Product Price List
CREATE TABLE supplier_product_prices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_id INT NOT NULL,
    product_id INT NOT NULL,
    unit_price DECIMAL(15,2) NOT NULL,
    moq DECIMAL(10,2) DEFAULT 1, -- Minimum Order Quantity
    lead_time_days INT DEFAULT 7, -- Lead time in days
    currency VARCHAR(3) DEFAULT 'IDR',
    is_active BOOLEAN DEFAULT TRUE,
    effective_from DATE,
    effective_to DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    UNIQUE KEY unique_supplier_product (supplier_id, product_id, effective_from)
);

-- Create indexes for better performance
CREATE INDEX idx_po_status ON purchase_orders(status);
CREATE INDEX idx_po_supplier ON purchase_orders(supplier_id);
CREATE INDEX idx_po_date ON purchase_orders(created_at);
CREATE INDEX idx_transaction_product ON inventory_transactions(product_id);
CREATE INDEX idx_transaction_warehouse ON inventory_transactions(warehouse_id);
CREATE INDEX idx_transaction_date ON inventory_transactions(created_at);
CREATE INDEX idx_batch_expiry ON product_batches(expiry_date);
CREATE INDEX idx_opname_warehouse ON stock_opname(warehouse_id);
CREATE INDEX idx_opname_period ON stock_opname(period);

-- Insert sample data
INSERT INTO purchase_orders (po_number, supplier_id, status, total_amount, expected_date, created_by) VALUES
('PO001', 1, 'approved', 15000000, DATE_ADD(CURDATE(), INTERVAL 7 DAY), 1);

INSERT INTO purchase_order_items (po_id, product_id, quantity, unit_price, total_price) VALUES
(1, 1, 100, 150000, 15000000);