-- =======================================================
-- Catalog Maker - Multi-Role PHP & MySQL Database Schema
-- Supports Retailer, Wholesaler, and Reseller accounts
-- =======================================================

CREATE DATABASE IF NOT EXISTS `catalog_maker_db` 
    DEFAULT CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE `catalog_maker_db`;

-- Users Table: Stores Retailers, Wholesalers, Resellers
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_uuid` VARCHAR(64) NOT NULL UNIQUE,
    `full_name` VARCHAR(150) NOT NULL,
    `business_name` VARCHAR(200) NOT NULL,
    `email` VARCHAR(150) NOT NULL UNIQUE,
    `phone` VARCHAR(50) DEFAULT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `account_type` ENUM('RETAILER', 'WHOLESALER', 'RESELLER') NOT NULL DEFAULT 'RETAILER',
    `business_category` VARCHAR(100) DEFAULT 'General',
    `tax_id` VARCHAR(50) DEFAULT NULL,
    `address` TEXT DEFAULT NULL,
    `auth_token` VARCHAR(255) DEFAULT NULL,
    `token_expires_at` DATETIME DEFAULT NULL,
    `reseller_margin_percent` DECIMAL(5,2) DEFAULT 15.00,
    `wholesale_moq` INT DEFAULT 10,
    `is_verified` TINYINT(1) DEFAULT 1,
    `status` ENUM('ACTIVE', 'SUSPENDED', 'PENDING') DEFAULT 'ACTIVE',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_email` (`email`),
    INDEX `idx_account_type` (`account_type`),
    INDEX `idx_auth_token` (`auth_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products Table: User-scoped inventory items
CREATE TABLE IF NOT EXISTS `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_uuid` VARCHAR(64) NOT NULL UNIQUE,
    `user_uuid` VARCHAR(64) NOT NULL,
    `name` VARCHAR(200) NOT NULL,
    `category` VARCHAR(100) NOT NULL DEFAULT 'General',
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `wholesale_price` DECIMAL(10,2) DEFAULT NULL,
    `reseller_price` DECIMAL(10,2) DEFAULT NULL,
    `mrp` DECIMAL(10,2) DEFAULT NULL,
    `unit` VARCHAR(20) DEFAULT 'pc',
    `sku` VARCHAR(50) DEFAULT NULL,
    `barcode` VARCHAR(50) DEFAULT NULL,
    `image_url` TEXT DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `in_stock` TINYINT(1) DEFAULT 1,
    `stock_quantity` INT DEFAULT 0,
    `badge` VARCHAR(50) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_uuid`) REFERENCES `users` (`user_uuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Catalogs Table: User-created digital brochures
CREATE TABLE IF NOT EXISTS `catalogs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `catalog_uuid` VARCHAR(64) NOT NULL UNIQUE,
    `user_uuid` VARCHAR(64) NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `subtitle` VARCHAR(255) DEFAULT NULL,
    `category` VARCHAR(100) DEFAULT 'General',
    `color_hex` VARCHAR(20) DEFAULT '#2563EB',
    `layout_type` VARCHAR(50) DEFAULT '2-column grid',
    `is_published` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_uuid`) REFERENCES `users` (`user_uuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;