<?php
require_once __DIR__ . '/src/db.php';

$sqls = [
    "CREATE TABLE IF NOT EXISTS `purchases` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `business_id` int(11) NOT NULL,
      `party_id` int(11) NOT NULL,
      `bill_no` varchar(50) DEFAULT NULL,
      `purchase_date` date DEFAULT NULL,
      `subtotal` decimal(12,2) DEFAULT 0.00,
      `total_tax` decimal(12,2) DEFAULT 0.00,
      `grand_total` decimal(12,2) DEFAULT 0.00,
      `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;",

    "CREATE TABLE IF NOT EXISTS `purchase_items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `purchase_id` int(11) NOT NULL,
      `item_id` int(11) NOT NULL,
      `qty` decimal(12,2) DEFAULT 0.00,
      `rate` decimal(12,2) DEFAULT 0.00,
      `amount` decimal(12,2) DEFAULT 0.00,
      `tax_per` decimal(5,2) DEFAULT 0.00,
      `tax_amt` decimal(12,2) DEFAULT 0.00,
      `net_value` decimal(12,2) DEFAULT 0.00,
      PRIMARY KEY (`id`),
      KEY `purchase_id` (`purchase_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;",

    "CREATE TABLE IF NOT EXISTS `payments` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `business_id` int(11) NOT NULL,
      `party_id` int(11) NOT NULL,
      `amount` decimal(12,2) NOT NULL,
      `payment_date` date NOT NULL,
      `payment_mode` varchar(50) NOT NULL,
      `payment_type` enum('RECEIPT','PAYMENT') NOT NULL,
      `remarks` text DEFAULT NULL,
      `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;",

    "ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `logo` VARCHAR(255) DEFAULT NULL;",

    "CREATE TABLE IF NOT EXISTS `orders` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `business_id` int(11) NOT NULL,
      `created_at` datetime NOT NULL DEFAULT current_timestamp(),
      `table_no` varchar(20) NOT NULL DEFAULT '',
      `status` varchar(15) NOT NULL DEFAULT 'OPEN',
      `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
      `discount` decimal(10,2) NOT NULL DEFAULT 0.00,
      `tax` decimal(10,2) NOT NULL DEFAULT 0.00,
      `total` decimal(10,2) NOT NULL DEFAULT 0.00,
      `payment_method` varchar(20) NOT NULL DEFAULT '',
      `gst_enabled` tinyint(1) NOT NULL DEFAULT 1,
      `customer_sno` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",

    "CREATE TABLE IF NOT EXISTS `order_items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `business_id` int(11) DEFAULT NULL,
      `order_id` int(11) NOT NULL,
      `item_id` int(11) DEFAULT NULL,
      `item_name` varchar(120) NOT NULL,
      `price` decimal(10,2) NOT NULL DEFAULT 0.00,
      `qty` int(11) NOT NULL DEFAULT 1,
      `notes` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    
];


foreach ($sqls as $sql) {
    query($sql);
    echo "Executed: " . substr($sql, 0, 50) . "...\n";
}

echo "Migration completed successfully.";
