-- ==========================================================================
-- ZITEO Database Schema
-- MySQL 8.0+ / MariaDB
-- ==========================================================================


-- ==========================================================================
-- Plans Table
-- ==========================================================================
CREATE TABLE IF NOT EXISTS `plans` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `price` INT NOT NULL DEFAULT 0 COMMENT 'Price in paise (4900 = ₹49)',
    `product_limit` INT NOT NULL DEFAULT 15,
    `features` JSON,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default plans
INSERT INTO `plans` (`name`, `price`, `product_limit`, `features`) VALUES
('Basic', 4900, 15, '{"whatsapp_orders": true}'),
('Standard', 9900, 50, '{"whatsapp_orders": true, "analytics": true, "custom_colors": true}'),
('Pro', 19900, 999, '{"whatsapp_orders": true, "analytics": true, "custom_colors": true, "custom_domain": true, "remove_branding": true}');

-- ==========================================================================
-- Stores Table
-- ==========================================================================
CREATE TABLE IF NOT EXISTS `stores` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `plan_id` INT UNSIGNED NOT NULL DEFAULT 1,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(50) NOT NULL,
    `whatsapp` VARCHAR(15) NOT NULL,
    `status` ENUM('pending', 'active', 'suspended') NOT NULL DEFAULT 'pending',
    `theme_config` JSON,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `slug` (`slug`),
    KEY `user_id` (`user_id`),
    KEY `plan_id` (`plan_id`),
    KEY `status` (`status`),
    CONSTRAINT `stores_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `stores_plan_fk` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- Products Table
-- ==========================================================================
CREATE TABLE IF NOT EXISTS `products` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `price` INT NOT NULL COMMENT 'Price in paise',
    `offer_price` INT DEFAULT NULL COMMENT 'Discounted price in paise',
    `image` VARCHAR(255) DEFAULT NULL,
    `stock_status` ENUM('in_stock', 'out_of_stock') NOT NULL DEFAULT 'in_stock',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `store_id` (`store_id`),
    KEY `is_active` (`is_active`),
    CONSTRAINT `products_store_fk` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- Customers Table (Buyers)
-- ==========================================================================
CREATE TABLE IF NOT EXISTS `customers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(15) DEFAULT NULL,
    `email` VARCHAR(255) DEFAULT NULL,
    `address` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `store_id` (`store_id`),
    CONSTRAINT `customers_store_fk` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- Orders Table
-- ==========================================================================
CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` INT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,
    `order_number` VARCHAR(20) NOT NULL,
    `items` JSON NOT NULL COMMENT 'Array of {product_id, name, quantity, price}',
    `total` INT NOT NULL COMMENT 'Total in paise',
    `status` ENUM('initiated', 'confirmed', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'initiated',
    `payment_status` ENUM('pending', 'paid', 'offline') NOT NULL DEFAULT 'offline',
    `notes` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `order_number` (`order_number`),
    KEY `store_id` (`store_id`),
    KEY `customer_id` (`customer_id`),
    KEY `status` (`status`),
    CONSTRAINT `orders_store_fk` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE,
    CONSTRAINT `orders_customer_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

