-- Purchase and Supplier Management Schema
-- Add to main database schema

-- Suppliers table
CREATE TABLE IF NOT EXISTS suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    franchise_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    address TEXT,
    payment_terms INT DEFAULT 30,
    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    INDEX idx_franchise_suppliers (franchise_id),
    INDEX idx_supplier_active (is_active)
);

-- Supplier products relationship table
CREATE TABLE IF NOT EXISTS supplier_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_id INT NOT NULL,
    product_id INT NOT NULL,
    supplier_price DECIMAL(10,2) NOT NULL,
    min_order_quantity INT DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_supplier_product (supplier_id, product_id),
    INDEX idx_supplier_products (supplier_id),
    INDEX idx_product_suppliers (product_id)
);

-- Purchases table
CREATE TABLE IF NOT EXISTS purchases (
    id INT PRIMARY KEY AUTO_INCREMENT,
    franchise_id INT NOT NULL,
    branch_id INT NOT NULL,
    supplier_id INT NOT NULL,
    user_id INT NOT NULL,
    purchase_number VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    notes TEXT,
    purchase_date DATE NOT NULL,
    status ENUM('pending', 'ordered', 'received', 'paid', 'cancelled') DEFAULT 'pending',
    payment_date DATE NULL,
    payment_method VARCHAR(50) NULL,
    payment_reference VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (franchise_id) REFERENCES franchises(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_franchise_purchases (franchise_id),
    INDEX idx_branch_purchases (branch_id),
    INDEX idx_supplier_purchases (supplier_id),
    INDEX idx_purchase_date (purchase_date),
    INDEX idx_purchase_status (status),
    INDEX idx_payment_date (payment_date)
);

-- Purchase items table
CREATE TABLE IF NOT EXISTS purchase_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_cost DECIMAL(10,2) NOT NULL,
    total_cost DECIMAL(10,2) NOT NULL,
    batch_number VARCHAR(100),
    expiration_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_purchase_items (purchase_id),
    INDEX idx_product_purchases (product_id),
    INDEX idx_expiration_date (expiration_date)
);

-- Insert sample data for testing
INSERT INTO suppliers (franchise_id, name, contact_person, email, phone, payment_terms, is_active) VALUES
(1, 'Distribuidora Central', 'Juan Pérez', 'ventas@distribuidoracentral.com', '+502 2234-5678', 30, 1),
(1, 'Lácteos del Valle', 'María González', 'pedidos@lacteosdelval.com', '+502 5678-9012', 15, 1),
(1, 'Frutas y Verduras SA', 'Carlos López', 'info@frutasyverduras.com', '+502 3456-7890', 7, 1);

-- Sample supplier products (assuming product IDs exist)
INSERT INTO supplier_products (supplier_id, product_id, supplier_price, min_order_quantity, is_active) VALUES
(1, 1, 15.50, 10, 1),
(1, 2, 12.75, 5, 1),
(2, 3, 8.25, 20, 1),
(3, 4, 5.00, 50, 1);