-- Create Orders and Transaction Tables
-- Run this to enable POS transactions

USE superpos;

-- Orders table
CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `invoice_number` VARCHAR(50) NOT NULL UNIQUE,
    `user_id` INT UNSIGNED NOT NULL,
    `contact_id` INT UNSIGNED NULL COMMENT 'Customer ID for sales, Supplier ID for purchases',
    `branch_id` INT UNSIGNED NOT NULL DEFAULT 1,
    `transaction_type` ENUM('Sale', 'Purchase') NOT NULL DEFAULT 'Sale',
    `order_source` ENUM(
        'Offline_POS',
        'Online_Shop',
        'Manual'
    ) NOT NULL DEFAULT 'Offline_POS',
    `order_status` ENUM(
        'Pending',
        'Processing',
        'Completed',
        'Cancelled'
    ) NOT NULL DEFAULT 'Pending',
    `subtotal` DECIMAL(15, 2) NOT NULL DEFAULT 0,
    `discount_type` ENUM('None', 'Percentage', 'Fixed') DEFAULT 'None',
    `discount_value` DECIMAL(10, 2) DEFAULT 0,
    `discount_amount` DECIMAL(15, 2) DEFAULT 0,
    `tax_percentage` DECIMAL(5, 2) DEFAULT 0,
    `tax_amount` DECIMAL(15, 2) DEFAULT 0,
    `shipping_cost` DECIMAL(15, 2) DEFAULT 0,
    `total_amount` DECIMAL(15, 2) NOT NULL,
    `payment_method` ENUM(
        'Cash',
        'Transfer',
        'E-Wallet',
        'Credit',
        'Debit_Card',
        'Online'
    ) NOT NULL DEFAULT 'Cash',
    `payment_status` ENUM(
        'Unpaid',
        'Partial',
        'Paid',
        'Refunded'
    ) NOT NULL DEFAULT 'Unpaid',
    `paid_amount` DECIMAL(15, 2) DEFAULT 0,
    `change_amount` DECIMAL(15, 2) DEFAULT 0,
    `due_date` DATE NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_invoice (`invoice_number`),
    INDEX idx_user (`user_id`),
    INDEX idx_contact (`contact_id`),
    INDEX idx_branch (`branch_id`),
    INDEX idx_status (`order_status`),
    INDEX idx_payment_status (`payment_status`),
    INDEX idx_created (`created_at`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Order Items table
CREATE TABLE IF NOT EXISTS `order_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity` DECIMAL(10, 2) NOT NULL,
    `unit_price` DECIMAL(15, 2) NOT NULL,
    `discount_percentage` DECIMAL(5, 2) DEFAULT 0,
    `discount_amount` DECIMAL(15, 2) DEFAULT 0,
    `subtotal` DECIMAL(15, 2) NOT NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
    INDEX idx_order (`order_id`),
    INDEX idx_product (`product_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Stock Mutations table
CREATE TABLE IF NOT EXISTS `stock_mutations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED NOT NULL DEFAULT 1,
    `mutation_type` ENUM(
        'In',
        'Out',
        'Adjustment',
        'Transfer_Out',
        'Transfer_In',
        'Return',
        'Opname'
    ) NOT NULL,
    `quantity` DECIMAL(10, 2) NOT NULL,
    `reference_type` ENUM(
        'Sale',
        'Purchase',
        'Transfer',
        'Adjustment',
        'Return',
        'Opname'
    ) NULL,
    `reference_id` INT UNSIGNED NULL COMMENT 'Order ID, Transfer ID, etc',
    `notes` TEXT,
    `user_id` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
    INDEX idx_product (`product_id`),
    INDEX idx_branch (`branch_id`),
    INDEX idx_type (`mutation_type`),
    INDEX idx_created (`created_at`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Branch Stocks table (if not exists)
CREATE TABLE IF NOT EXISTS `branch_stocks` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED NOT NULL DEFAULT 1,
    `stock_quantity` DECIMAL(10, 2) NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_product_branch (`product_id`, `branch_id`),
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    INDEX idx_branch (`branch_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

SELECT 'Tables created successfully!' as Status;