<?php
require 'config/db.php';

try {
    $pdo->beginTransaction();

    // 1. Create tbl_bookings table
    $sqlBooking = "CREATE TABLE IF NOT EXISTS tbl_bookings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        booking_ref_id VARCHAR(50) NOT NULL COMMENT 'Internal Order ID',
        waybill_no VARCHAR(50) DEFAULT NULL COMMENT 'AWB Number from Courier',
        
        -- Relationships
        courier_id INT NOT NULL,
        pickup_point_id INT NOT NULL,
        
        -- Consignee Details
        consignee_name VARCHAR(100) NOT NULL,
        consignee_phone VARCHAR(20) NOT NULL,
        consignee_email VARCHAR(100) DEFAULT NULL,
        consignee_address TEXT NOT NULL,
        consignee_pin VARCHAR(10) NOT NULL,
        consignee_city VARCHAR(50) DEFAULT NULL,
        consignee_state VARCHAR(50) DEFAULT NULL,
        consignee_country VARCHAR(50) DEFAULT 'India',
        
        -- Shipment Details
        payment_mode ENUM('Prepaid', 'COD', 'Pickup', 'REPL') DEFAULT 'Prepaid',
        cod_amount DECIMAL(10,2) DEFAULT 0.00,
        weight DECIMAL(10,2) DEFAULT NULL COMMENT 'Weight in gms',
        length DECIMAL(10,2) DEFAULT NULL,
        width DECIMAL(10,2) DEFAULT NULL,
        height DECIMAL(10,2) DEFAULT NULL,
        shipping_mode VARCHAR(20) DEFAULT 'Surface',
        product_desc TEXT DEFAULT NULL,
        quantity INT DEFAULT 1,
        
        -- MPS Fields (Optional)
        is_mps TINYINT(1) DEFAULT 0,
        mps_master_id VARCHAR(50) DEFAULT NULL,
        
        -- API Data
        api_response JSON DEFAULT NULL COMMENT 'Full JSON response from Booking API',
        last_status VARCHAR(50) DEFAULT 'Pending' COMMENT 'Current Shipment Status',
        
        -- Audit Fields
        created_by INT DEFAULT NULL,
        updated_by INT DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

        INDEX (booking_ref_id),
        INDEX (waybill_no),
        INDEX (payment_mode)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

    $pdo->exec($sqlBooking);
    echo "Table 'tbl_bookings' created successfully.<br>";

    // 2. Create tbl_tracking table
    $sqlTracking = "CREATE TABLE IF NOT EXISTS tbl_tracking (
        id INT AUTO_INCREMENT PRIMARY KEY,
        booking_id INT NOT NULL,
        waybill_no VARCHAR(50) NOT NULL,
        
        -- Tracking Details
        scan_type VARCHAR(50) DEFAULT NULL,
        scan_location VARCHAR(100) DEFAULT NULL,
        scan_datetime DATETIME DEFAULT NULL,
        status_code VARCHAR(50) DEFAULT NULL,
        remarks TEXT DEFAULT NULL,
        
        raw_response JSON DEFAULT NULL COMMENT 'Full JSON response from Tracking API',
        
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        
        FOREIGN KEY (booking_id) REFERENCES tbl_bookings(id) ON DELETE CASCADE,
        INDEX (waybill_no)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

    $pdo->exec($sqlTracking);
    echo "Table 'tbl_tracking' created successfully.<br>";

    $pdo->commit();

} catch (PDOException $e) {
    $pdo->rollBack();
    die("DB Error: " . $e->getMessage());
}
?>