<?php
/**
 * Create Store Customer Pricing Table
 * This table stores package-specific prices for regular customers (not agents)
 * Different from store_pricing which uses ranges for agents
 */

require_once 'config.php';

// Create table - Structure matches existing store_pricing table conventions
$sql = "CREATE TABLE IF NOT EXISTS `store_customer_pricing` (
    `id` int NOT NULL AUTO_INCREMENT,
    `store_id` int NOT NULL,
    `network_type` enum('mtn','telecel','airtel_tigo') COLLATE utf8mb4_unicode_ci NOT NULL,
    `package_size_gb` decimal(10,4) NOT NULL COMMENT 'Package size in GB (e.g., 1.0, 2.0, 5.0)',
    `package_label` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Package label (e.g., 1GB, 2GB, 5GB)',
    `price` decimal(10,2) NOT NULL COMMENT 'Fixed price for this specific package',
    `is_active` tinyint(1) DEFAULT '1',
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_store_network_package` (`store_id`, `network_type`, `package_size_gb`),
    KEY `store_id` (`store_id`),
    KEY `network_type` (`network_type`),
    KEY `is_active` (`is_active`),
    CONSTRAINT `fk_store_customer_pricing_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql)) {
    echo "Table 'store_customer_pricing' created successfully!\n";
    echo "\nTable structure:\n";
    echo "- id: Primary key\n";
    echo "- store_id: Foreign key to stores table\n";
    echo "- network_type: Network (mtn, etc.)\n";
    echo "- package_size_gb: Package size in GB (e.g., 1.0, 2.0, 5.0)\n";
    echo "- package_label: Display label (e.g., '1GB', '2GB')\n";
    echo "- price: Fixed price for this specific package\n";
    echo "- is_active: Whether this pricing is active\n";
    echo "- created_at/updated_at: Timestamps\n";
    echo "\nUnique constraint: One price per store per network per package size\n";
} else {
    echo "Error creating table: " . $conn->error . "\n";
}

$conn->close();
?>

