-- SuperPOS Database Schema
-- MySQL 8.0+
-- Created: 2026-01-10



CREATE TABLE `shop_settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `shop_name` VARCHAR(150) NOT NULL,
    `address` TEXT,
    `phone` VARCHAR(20),
    `email` VARCHAR(100),
    `logo_url` VARCHAR(255),
    `printer_type` ENUM('Bluetooth', 'LAN', 'USB') DEFAULT 'Bluetooth',
    `printer_mac_address` VARCHAR(17),
    `printer_ip` VARCHAR(15),
    `footer_receipt` TEXT,
    `currency` VARCHAR(3) DEFAULT 'IDR',
    `timezone` VARCHAR(50) DEFAULT 'Asia/Jakarta',
    `olshop_enabled` BOOLEAN DEFAULT FALSE,
    `olshop_banner_url` VARCHAR(255),
    `olshop_location` VARCHAR(255),
    `bank_account_name` VARCHAR(100),
    `bank_account_number` VARCHAR(50),
    `bank_name` VARCHAR(100),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================
-- 2. MASTER DATA MODULE (Products & Inventory)
-- ============================================


CREATE TABLE `categories` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `icon` VARCHAR(50),
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (`is_active`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `products` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `sku` VARCHAR(50) NOT NULL UNIQUE COMMENT 'Barcode/SKU',
    `name` VARCHAR(200) NOT NULL,
    `category_id` INT UNSIGNED,
    `product_type` ENUM('Barang', 'Jasa', 'Paket') NOT NULL DEFAULT 'Barang' COMMENT 'Barang=physical stock, Jasa=no stock, Paket=bundle',
    `unit` VARCHAR(20) DEFAULT 'Pcs' COMMENT 'Unit: Pcs, Kg, Box, etc',
    `description` TEXT,
    `image_url` VARCHAR(255),
    `base_buy_price` DECIMAL(15, 2) DEFAULT 0 COMMENT 'HPP/COGS',
    `base_sell_price` DECIMAL(15, 2) NOT NULL COMMENT 'Default selling price',
    `stock_current` DECIMAL(10, 2) DEFAULT 0 COMMENT 'Current stock quantity',
    `min_stock_alert` DECIMAL(10, 2) DEFAULT 0 COMMENT 'Low stock threshold',
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
    INDEX idx_sku (`sku`),
    INDEX idx_type (`product_type`),
    INDEX idx_active (`is_active`),
    INDEX idx_stock (`stock_current`),
    INDEX idx_category (`category_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `product_bundles` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `parent_product_id` INT UNSIGNED NOT NULL COMMENT 'The Paket product',
    `child_product_id` INT UNSIGNED NOT NULL COMMENT 'Item inside the bundle',
    `quantity_needed` DECIMAL(10, 2) NOT NULL DEFAULT 1 COMMENT 'Qty of child per bundle',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`parent_product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`child_product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    UNIQUE KEY unique_bundle_item (
        `parent_product_id`,
        `child_product_id`
    ),
    INDEX idx_parent (`parent_product_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `price_levels` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT UNSIGNED NOT NULL,
    `level_name` VARCHAR(50) NOT NULL COMMENT 'e.g: Reseller, VIP, Grosir',
    `price` DECIMAL(15, 2) NOT NULL,
    `min_qty` DECIMAL(10, 2) DEFAULT 1 COMMENT 'Minimum quantity to get this price',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    INDEX idx_product (`product_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `stock_mutations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT UNSIGNED NOT NULL,
    `type` ENUM(
        'Penjualan',
        'Pembelian',
        'Retur Jual',
        'Retur Beli',
        'Opname',
        'Restock',
        'Adjustment'
    ) NOT NULL,
    `quantity` DECIMAL(10, 2) NOT NULL COMMENT 'Positive=in, Negative=out',
    `current_stock_snapshot` DECIMAL(10, 2) NOT NULL COMMENT 'Stock after mutation',
    `reference_id` INT UNSIGNED COMMENT 'Related order_id or other reference',
    `reference_type` VARCHAR(50) COMMENT 'orders, returns, etc',
    `notes` TEXT,
    `user_id` INT UNSIGNED COMMENT 'Who performed this action',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    INDEX idx_product (`product_id`),
    INDEX idx_type (`type`),
    INDEX idx_created (`created_at`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `credit_records` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `contact_id` INT UNSIGNED NOT NULL,
    `type` ENUM('Receivable', 'Payable') NOT NULL COMMENT 'Receivable=Piutang, Payable=Hutang',
    `transaction_type` ENUM(
        'New Credit',
        'Payment',
        'Adjustment'
    ) NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `balance_after` DECIMAL(15, 2) NOT NULL,
    `due_date` DATE,
    `payment_date` DATE,
    `status` ENUM(
        'Unpaid',
        'Partial',
        'Paid',
        'Overdue'
    ) DEFAULT 'Unpaid',
    `reference_id` INT UNSIGNED COMMENT 'Related order_id',
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
    INDEX idx_contact (`contact_id`),
    INDEX idx_type (`type`),
    INDEX idx_status (`status`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================
-- 4. TRANSACTION MODULE
-- ============================================


CREATE TABLE `orders` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `invoice_number` VARCHAR(50) NOT NULL UNIQUE,
    `user_id` INT UNSIGNED NOT NULL COMMENT 'Cashier/Staff who created this',
    `contact_id` INT UNSIGNED COMMENT 'Customer or Supplier',
    `transaction_type` ENUM('Sale', 'Purchase') NOT NULL,
    `order_source` ENUM(
        'Offline_POS',
        'Olshop',
        'Manual'
    ) DEFAULT 'Offline_POS',
    `order_status` ENUM(
        'Pending',
        'Paid',
        'Partial',
        'Shipped',
        'Completed',
        'Cancelled',
        'Returned'
    ) DEFAULT 'Pending',
    `subtotal` DECIMAL(15, 2) NOT NULL DEFAULT 0,
    `discount_type` ENUM('Percentage', 'Fixed', 'None') DEFAULT 'None',
    `discount_value` DECIMAL(15, 2) DEFAULT 0,
    `discount_amount` DECIMAL(15, 2) DEFAULT 0 COMMENT 'Calculated discount',
    `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 COMMENT 'Final amount',
    `payment_method` ENUM(
        'Cash',
        'Transfer',
        'Credit',
        'E-Wallet',
        'Debit Card'
    ) DEFAULT 'Cash',
    `payment_status` ENUM('Unpaid', 'Partial', 'Paid') DEFAULT 'Unpaid',
    `paid_amount` DECIMAL(15, 2) DEFAULT 0,
    `change_amount` DECIMAL(15, 2) DEFAULT 0,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE SET NULL,
    INDEX idx_invoice (`invoice_number`),
    INDEX idx_type (`transaction_type`),
    INDEX idx_source (`order_source`),
    INDEX idx_status (`order_status`),
    INDEX idx_created (`created_at`),
    INDEX idx_contact (`contact_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `order_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `product_name` VARCHAR(200) NOT NULL COMMENT 'Snapshot at time of sale',
    `quantity` DECIMAL(10, 2) NOT NULL,
    `unit` VARCHAR(20),
    `price_at_moment` DECIMAL(15, 2) NOT NULL COMMENT 'Selling price at transaction time',
    `cogs_at_moment` DECIMAL(15, 2) DEFAULT 0 COMMENT 'Buy price at transaction time for P&L',
    `discount_per_item` DECIMAL(15, 2) DEFAULT 0,
    `subtotal` DECIMAL(15, 2) NOT NULL COMMENT 'qty * price',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE RESTRICT,
    INDEX idx_order (`order_id`),
    INDEX idx_product (`product_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `returns` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `return_number` VARCHAR(50) NOT NULL UNIQUE,
    `order_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL COMMENT 'Who processed the return',
    `return_type` ENUM(
        'Sale Return',
        'Purchase Return'
    ) NOT NULL,
    `reason` TEXT,
    `refund_amount` DECIMAL(15, 2) NOT NULL,
    `refund_method` ENUM(
        'Cash',
        'Transfer',
        'Store Credit'
    ) DEFAULT 'Cash',
    `status` ENUM(
        'Pending',
        'Approved',
        'Rejected',
        'Completed'
    ) DEFAULT 'Pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    INDEX idx_order (`order_id`),
    INDEX idx_status (`status`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `return_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `return_id` INT UNSIGNED NOT NULL,
    `order_item_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity_returned` DECIMAL(10, 2) NOT NULL,
    `price_at_return` DECIMAL(15, 2) NOT NULL,
    `subtotal` DECIMAL(15, 2) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`return_id`) REFERENCES `returns` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE RESTRICT,
    INDEX idx_return (`return_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================
-- 5. FINANCIAL MODULE
-- ============================================


CREATE TABLE `cash_registers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL COMMENT 'Cashier',
    `opening_cash` DECIMAL(15, 2) NOT NULL COMMENT 'Starting cash balance',
    `closing_cash` DECIMAL(15, 2) COMMENT 'System calculated closing',
    `actual_cash` DECIMAL(15, 2) COMMENT 'Physical cash count',
    `difference` DECIMAL(15, 2) COMMENT 'actual - closing',
    `status` ENUM('Open', 'Closed') DEFAULT 'Open',
    `opened_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `closed_at` TIMESTAMP NULL,
    `notes` TEXT,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    INDEX idx_user (`user_id`),
    INDEX idx_status (`status`),
    INDEX idx_opened (`opened_at`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `financial_records` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `type` ENUM('Income', 'Expense') NOT NULL,
    `category` VARCHAR(100) NOT NULL COMMENT 'e.g: Listrik, Gaji, Penjualan Tunai',
    `amount` DECIMAL(15, 2) NOT NULL,
    `description` TEXT,
    `transaction_date` DATE NOT NULL,
    `related_order_id` INT UNSIGNED COMMENT 'Link to order if transaction-related',
    `user_id` INT UNSIGNED COMMENT 'Who recorded this',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`related_order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    INDEX idx_type (`type`),
    INDEX idx_category (`category`),
    INDEX idx_date (`transaction_date`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================
-- 6. ONLINE SHOP MODULE
-- ============================================


CREATE TABLE `online_orders` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT UNSIGNED NOT NULL COMMENT 'Reference to main orders table',
    `platform` VARCHAR(50) COMMENT 'Shopee, Tokopedia, Website, etc',
    `platform_order_id` VARCHAR(100) COMMENT 'Order ID from platform',
    `customer_name` VARCHAR(150),
    `customer_phone` VARCHAR(20),
    `shipping_address` TEXT,
    `shipping_courier` VARCHAR(50),
    `tracking_number` VARCHAR(100),
    `shipping_status` ENUM(
        'Pending',
        'Processing',
        'Shipped',
        'Delivered',
        'Cancelled'
    ) DEFAULT 'Pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
    INDEX idx_order (`order_id`),
    INDEX idx_platform (`platform`),
    INDEX idx_status (`shipping_status`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `sync_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `device_id` VARCHAR(100) NOT NULL COMMENT 'Mobile device identifier',
    `user_id` INT UNSIGNED,
    `sync_type` ENUM(
        'Upload',
        'Download',
        'Conflict'
    ) NOT NULL,
    `table_name` VARCHAR(50),
    `records_count` INT DEFAULT 0,
    `status` ENUM(
        'Success',
        'Failed',
        'Partial'
    ) DEFAULT 'Success',
    `error_message` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    INDEX idx_device (`device_id`),
    INDEX idx_created (`created_at`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


-- Shop settings default
INSERT INTO
    `shop_settings` (
        `shop_name`,
        `address`,
        `phone`,
        `footer_receipt`,
        `currency`,
        `timezone`
    )
VALUES (
        'SuperPOS Store',
        'Jl. Contoh No. 123, Jakarta',
        '081234567890',
        'Terima kasih atas kunjungan Anda!',
        'IDR',
        'Asia/Jakarta'
    );

-- Sample categories
INSERT INTO
    `categories` (
        `name`,
        `description`,
        `is_active`
    )
VALUES (
        'Elektronik',
        'Produk elektronik dan gadget',
        TRUE
    ),
    (
        'Fashion',
        'Pakaian dan aksesoris',
        TRUE
    ),
    (
        'Makanan & Minuman',
        'Produk konsumsi',
        TRUE
    ),
    ('Jasa', 'Layanan jasa', TRUE);

-- Sample products
INSERT INTO
    `products` (
        `sku`,
        `name`,
        `category_id`,
        `product_type`,
        `unit`,
        `base_buy_price`,
        `base_sell_price`,
        `stock_current`,
        `min_stock_alert`
    )
VALUES (
        'P001',
        'Smartphone Samsung A54',
        1,
        'Barang',
        'Pcs',
        3500000,
        4200000,
        10,
        2
    ),
    (
        'P002',
        'Kaos Polos Hitam',
        2,
        'Barang',
        'Pcs',
        25000,
        50000,
        50,
        10
    ),
    (
        'P003',
        'Service HP',
        4,
        'Jasa',
        'Unit',
        0,
        150000,
        0,
        0
    ),
    (
        'P004',
        'Paket Hemat HP + Case',
        1,
        'Paket',
        'Paket',
        0,
        4300000,
        0,
        0
    );

-- Sample bundle (Paket contains P001 and accessories)
INSERT INTO
    `product_bundles` (
        `parent_product_id`,
        `child_product_id`,
        `quantity_needed`
    )
VALUES (4, 1, 1);