-- ============================================================
-- SalonPro X - Enterprise Database Schema
-- MySQL 8.0+ | Engine: InnoDB | Charset: utf8mb4
-- ============================================================

CREATE DATABASE IF NOT EXISTS `salonprox_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `salonprox_db`;

SET foreign_key_checks = 0;

-- ============================================================
-- 1. CORE & IDENTITY TABLES
-- ============================================================

-- Branches / Outlets
CREATE TABLE `branches` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `address` TEXT,
    `city` VARCHAR(100),
    `state` VARCHAR(100),
    `zip` VARCHAR(20),
    `phone` VARCHAR(20),
    `email` VARCHAR(100),
    `tax_id` VARCHAR(50),
    `logo` VARCHAR(255),
    `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `branches_status_idx` (`status`),
    INDEX `branches_slug_idx` (`slug`)
) ENGINE=InnoDB;

-- Roles
CREATE TABLE `roles` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `slug` VARCHAR(60) NOT NULL UNIQUE,
    `description` TEXT,
    `is_system` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `roles_slug_idx` (`slug`)
) ENGINE=InnoDB;

-- Permissions
CREATE TABLE `permissions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `module` VARCHAR(50) NOT NULL,
    `description` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `permissions_slug_idx` (`slug`),
    INDEX `permissions_module_idx` (`module`)
) ENGINE=InnoDB;

-- Role-Permission Junction
CREATE TABLE `role_permissions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `role_id` INT UNSIGNED NOT NULL,
    `permission_id` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_role_permission` (`role_id`, `permission_id`),
    CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_rp_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Users
CREATE TABLE `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `role_id` INT UNSIGNED,
    `employee_id` INT UNSIGNED NULL,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `phone` VARCHAR(20),
    `avatar` VARCHAR(255),
    `status` ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    `is_superadmin` TINYINT(1) NOT NULL DEFAULT 0,
    `last_login` DATETIME,
    `ip_address` VARCHAR(45),
    `remember_token` VARCHAR(255),
    `password_reset_token` VARCHAR(255),
    `password_reset_expires` DATETIME,
    `password_changed_at` DATETIME,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `users_email_idx` (`email`),
    INDEX `users_status_idx` (`status`),
    INDEX `users_branch_idx` (`branch_id`),
    INDEX `users_role_idx` (`role_id`),
    CONSTRAINT `fk_users_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Sessions
CREATE TABLE `sessions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `token` VARCHAR(255) NOT NULL,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `expires_at` DATETIME NOT NULL,
    `last_activity` DATETIME,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `sessions_user_idx` (`user_id`),
    INDEX `sessions_token_idx` (`token`),
    INDEX `sessions_expires_idx` (`expires_at`),
    CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 2. CUSTOMER MANAGEMENT (CRM)
-- ============================================================

-- Customers
CREATE TABLE `customers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `user_id` INT UNSIGNED NULL,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100),
    `phone` VARCHAR(20),
    `mobile` VARCHAR(20),
    `date_of_birth` DATE,
    `gender` ENUM('male','female','other') NULL,
    `address` TEXT,
    `city` VARCHAR(100),
    `state` VARCHAR(100),
    `zip` VARCHAR(20),
    `country` VARCHAR(100) DEFAULT 'Sri Lanka',
    `photo` VARCHAR(255),
    `notes` TEXT,
    `allergies` TEXT,
    `hair_history` TEXT,
    `skin_notes` TEXT,
    `favorite_stylist_id` INT UNSIGNED NULL,
    `source` VARCHAR(50),
    `referral_source` VARCHAR(100),
    `tags` TEXT,
    `total_visits` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_spent` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `last_visit_date` DATETIME,
    `status` ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `customers_name_idx` (`first_name`, `last_name`),
    INDEX `customers_email_idx` (`email`),
    INDEX `customers_phone_idx` (`phone`),
    INDEX `customers_status_idx` (`status`),
    INDEX `customers_branch_idx` (`branch_id`),
    CONSTRAINT `fk_customers_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_customers_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Customer Favorite Services
CREATE TABLE `customer_favorite_services` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT UNSIGNED NOT NULL,
    `service_id` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_customer_service` (`customer_id`, `service_id`),
    CONSTRAINT `fk_cfs_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cfs_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Customer Photos
CREATE TABLE `customer_photos` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT UNSIGNED NOT NULL,
    `photo` VARCHAR(255) NOT NULL,
    `caption` VARCHAR(255),
    `uploaded_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `cp_customer_idx` (`customer_id`),
    CONSTRAINT `fk_cp_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cp_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Customer Notes
CREATE TABLE `customer_notes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT UNSIGNED NOT NULL,
    `note` TEXT NOT NULL,
    `type` ENUM('general','allergy','preference','complaint','feedback') DEFAULT 'general',
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `cn_customer_idx` (`customer_id`),
    CONSTRAINT `fk_cn_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cn_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Customer Tags
CREATE TABLE `customer_tags` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `slug` VARCHAR(60) NOT NULL UNIQUE,
    `color` VARCHAR(7) DEFAULT '#C8A44E',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Customer-Tag Junction
CREATE TABLE `customer_tag_map` (
    `customer_id` INT UNSIGNED NOT NULL,
    `tag_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`customer_id`, `tag_id`),
    CONSTRAINT `fk_ctm_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ctm_tag` FOREIGN KEY (`tag_id`) REFERENCES `customer_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 3. MEMBERSHIP & LOYALTY
-- ============================================================

-- Membership Tiers
CREATE TABLE `membership_tiers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `slug` VARCHAR(60) NOT NULL UNIQUE,
    `duration_days` INT UNSIGNED NOT NULL DEFAULT 30,
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `discount_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `priority` INT UNSIGNED NOT NULL DEFAULT 0,
    `color` VARCHAR(7) DEFAULT '#C8A44E',
    `benefits` TEXT,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Customer Memberships
CREATE TABLE `customer_memberships` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT UNSIGNED NOT NULL,
    `tier_id` INT UNSIGNED NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `status` ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
    `activated_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `cancelled_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `cm_customer_idx` (`customer_id`),
    INDEX `cm_status_idx` (`status`),
    CONSTRAINT `fk_cm_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cm_tier` FOREIGN KEY (`tier_id`) REFERENCES `membership_tiers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Loyalty Points
CREATE TABLE `loyalty_points` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT UNSIGNED NOT NULL,
    `points_earned` INT NOT NULL DEFAULT 0,
    `points_redeemed` INT NOT NULL DEFAULT 0,
    `balance` INT NOT NULL DEFAULT 0,
    `reference_type` VARCHAR(50),
    `reference_id` INT UNSIGNED,
    `expiry_date` DATE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `lp_customer_idx` (`customer_id`),
    CONSTRAINT `fk_lp_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Loyalty Rules
CREATE TABLE `loyalty_rules` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `points_per_amount` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    `amount_threshold` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `min_purchase` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `max_points_per_transaction` INT UNSIGNED DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Gift Cards
CREATE TABLE `gift_cards` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `customer_id` INT UNSIGNED NULL,
    `card_number` VARCHAR(20) NOT NULL UNIQUE,
    `pin` VARCHAR(255),
    `initial_balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `current_balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `issue_date` DATE NOT NULL,
    `expiry_date` DATE,
    `status` ENUM('active','inactive','expired','redeemed') NOT NULL DEFAULT 'active',
    `issued_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `gc_number_idx` (`card_number`),
    INDEX `gc_status_idx` (`status`),
    CONSTRAINT `fk_gc_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_gc_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Gift Card Transactions
CREATE TABLE `gift_card_transactions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `gift_card_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `type` ENUM('load','redeem','refund') NOT NULL,
    `reference_type` VARCHAR(50),
    `reference_id` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `gct_card_idx` (`gift_card_id`),
    CONSTRAINT `fk_gct_card` FOREIGN KEY (`gift_card_id`) REFERENCES `gift_cards` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 4. SERVICES & INVENTORY
-- ============================================================

-- Service Categories
CREATE TABLE `service_categories` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `description` TEXT,
    `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `sc_branch_idx` (`branch_id`)
) ENGINE=InnoDB;

-- Services
CREATE TABLE `services` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `category_id` INT UNSIGNED,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `description` TEXT,
    `duration_minutes` INT UNSIGNED NOT NULL DEFAULT 30,
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `cost_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `tax_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `color` VARCHAR(7) DEFAULT '#C8A44E',
    `image` VARCHAR(255),
    `commission_type` ENUM('percentage','fixed') DEFAULT 'percentage',
    `commission_value` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `prep_time` INT UNSIGNED NOT NULL DEFAULT 0,
    `buffer_time` INT UNSIGNED NOT NULL DEFAULT 5,
    `is_online_bookable` TINYINT(1) NOT NULL DEFAULT 1,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `services_category_idx` (`category_id`),
    INDEX `services_active_idx` (`is_active`),
    INDEX `services_branch_idx` (`branch_id`),
    CONSTRAINT `fk_services_category` FOREIGN KEY (`category_id`) REFERENCES `service_categories` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_services_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Service-Employee Junction
CREATE TABLE `service_employees` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `service_id` INT UNSIGNED NOT NULL,
    `employee_id` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_service_employee` (`service_id`, `employee_id`),
    CONSTRAINT `fk_se_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_se_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Packages
CREATE TABLE `packages` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `description` TEXT,
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `tax_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `validity_days` INT UNSIGNED NOT NULL DEFAULT 30,
    `max_visits` INT UNSIGNED DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Package Services
CREATE TABLE `package_services` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `package_id` INT UNSIGNED NOT NULL,
    `service_id` INT UNSIGNED NOT NULL,
    `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_package_service` (`package_id`, `service_id`),
    CONSTRAINT `fk_ps_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ps_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Product Categories
CREATE TABLE `product_categories` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `description` TEXT,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Products (Inventory)
CREATE TABLE `products` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `category_id` INT UNSIGNED,
    `supplier_id` INT UNSIGNED,
    `name` VARCHAR(200) NOT NULL,
    `sku` VARCHAR(50) NOT NULL UNIQUE,
    `barcode` VARCHAR(50),
    `qr_code` VARCHAR(255),
    `description` TEXT,
    `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `cost_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `tax_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `reorder_level` INT UNSIGNED NOT NULL DEFAULT 10,
    `current_stock` INT NOT NULL DEFAULT 0,
    `min_stock` INT NOT NULL DEFAULT 0,
    `max_stock` INT DEFAULT NULL,
    `unit_type` VARCHAR(20) DEFAULT 'piece',
    `track_inventory` TINYINT(1) NOT NULL DEFAULT 1,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `products_sku_idx` (`sku`),
    INDEX `products_barcode_idx` (`barcode`),
    INDEX `products_category_idx` (`category_id`),
    INDEX `products_supplier_idx` (`supplier_id`),
    INDEX `products_active_idx` (`is_active`),
    INDEX `products_branch_idx` (`branch_id`),
    CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_products_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_products_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Product Batches (for expiry tracking)
CREATE TABLE `product_batches` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT UNSIGNED NOT NULL,
    `batch_number` VARCHAR(50) NOT NULL,
    `expiry_date` DATE,
    `quantity` INT NOT NULL DEFAULT 0,
    `cost_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `received_date` DATE NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `pb_product_idx` (`product_id`),
    INDEX `pb_expiry_idx` (`expiry_date`),
    CONSTRAINT `fk_pb_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Suppliers
CREATE TABLE `suppliers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `name` VARCHAR(200) NOT NULL,
    `contact_person` VARCHAR(100),
    `email` VARCHAR(100),
    `phone` VARCHAR(20),
    `mobile` VARCHAR(20),
    `address` TEXT,
    `city` VARCHAR(100),
    `state` VARCHAR(100),
    `tax_id` VARCHAR(50),
    `payment_terms` VARCHAR(100),
    `notes` TEXT,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `suppliers_name_idx` (`name`),
    INDEX `suppliers_branch_idx` (`branch_id`),
    CONSTRAINT `fk_suppliers_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Purchase Orders
CREATE TABLE `purchase_orders` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `supplier_id` INT UNSIGNED NOT NULL,
    `order_number` VARCHAR(50) NOT NULL UNIQUE,
    `order_date` DATE NOT NULL,
    `expected_date` DATE,
    `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('draft','pending','approved','received','cancelled') NOT NULL DEFAULT 'draft',
    `notes` TEXT,
    `approved_by` INT UNSIGNED,
    `approved_at` DATETIME,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `po_number_idx` (`order_number`),
    INDEX `po_supplier_idx` (`supplier_id`),
    INDEX `po_status_idx` (`status`),
    INDEX `po_branch_idx` (`branch_id`),
    CONSTRAINT `fk_po_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_po_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Purchase Order Items
CREATE TABLE `purchase_order_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `purchase_order_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity` INT NOT NULL DEFAULT 0,
    `unit_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `total_cost` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `poi_po_idx` (`purchase_order_id`),
    CONSTRAINT `fk_poi_po` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_poi_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Goods Received Notes
CREATE TABLE `goods_received_notes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `purchase_order_id` INT UNSIGNED,
    `grn_number` VARCHAR(50) NOT NULL UNIQUE,
    `received_date` DATE NOT NULL,
    `notes` TEXT,
    `received_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `grn_po_idx` (`purchase_order_id`),
    CONSTRAINT `fk_grn_po` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- GRN Items
CREATE TABLE `grn_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `grn_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity_ordered` INT NOT NULL DEFAULT 0,
    `quantity_received` INT NOT NULL DEFAULT 0,
    `quantity_accepted` INT NOT NULL DEFAULT 0,
    `quantity_rejected` INT NOT NULL DEFAULT 0,
    `unit_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `batch_number` VARCHAR(50),
    `expiry_date` DATE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `grni_grn_idx` (`grn_id`),
    CONSTRAINT `fk_grni_grn` FOREIGN KEY (`grn_id`) REFERENCES `goods_received_notes` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_grni_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Stock Transfers
CREATE TABLE `stock_transfers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `from_branch_id` INT UNSIGNED,
    `to_branch_id` INT UNSIGNED,
    `transfer_date` DATE NOT NULL,
    `status` ENUM('pending','in_transit','completed','cancelled') NOT NULL DEFAULT 'pending',
    `notes` TEXT,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `st_from_idx` (`from_branch_id`),
    INDEX `st_to_idx` (`to_branch_id`),
    CONSTRAINT `fk_st_from` FOREIGN KEY (`from_branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_st_to` FOREIGN KEY (`to_branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Stock Transfer Items
CREATE TABLE `stock_transfer_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `stock_transfer_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `sti_transfer_idx` (`stock_transfer_id`),
    CONSTRAINT `fk_sti_transfer` FOREIGN KEY (`stock_transfer_id`) REFERENCES `stock_transfers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sti_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Stock Adjustments
CREATE TABLE `stock_adjustments` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `product_id` INT UNSIGNED NOT NULL,
    `adjustment_type` ENUM('addition','reduction','damage','expiry','loss','correction') NOT NULL,
    `quantity` INT NOT NULL,
    `current_stock_before` INT NOT NULL DEFAULT 0,
    `current_stock_after` INT NOT NULL DEFAULT 0,
    `reason` TEXT,
    `reference_number` VARCHAR(50),
    `adjusted_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `sa_branch_idx` (`branch_id`),
    INDEX `sa_product_idx` (`product_id`),
    CONSTRAINT `fk_sa_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_sa_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Stock Movements (Audit Trail for Inventory)
CREATE TABLE `stock_movements` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED,
    `movement_type` ENUM('purchase','sale','transfer_in','transfer_out','adjustment','return','opening_stock') NOT NULL,
    `quantity` INT NOT NULL,
    `before_quantity` INT NOT NULL DEFAULT 0,
    `after_quantity` INT NOT NULL DEFAULT 0,
    `reference_type` VARCHAR(50),
    `reference_id` INT UNSIGNED,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `sm_product_idx` (`product_id`),
    INDEX `sm_branch_idx` (`branch_id`),
    INDEX `sm_type_idx` (`movement_type`),
    CONSTRAINT `fk_sm_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 5. APPOINTMENTS & POS
-- ============================================================

-- Appointments
CREATE TABLE `appointments` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `customer_id` INT UNSIGNED NOT NULL,
    `start_datetime` DATETIME NOT NULL,
    `end_datetime` DATETIME NOT NULL,
    `status` ENUM('pending','confirmed','checked_in','in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'pending',
    `notes` TEXT,
    `reminder_sent` TINYINT(1) NOT NULL DEFAULT 0,
    `checked_in_at` DATETIME,
    `checked_out_at` DATETIME,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `appointments_date_idx` (`start_datetime`),
    INDEX `appointments_customer_idx` (`customer_id`),
    INDEX `appointments_status_idx` (`status`),
    INDEX `appointments_branch_idx` (`branch_id`),
    CONSTRAINT `fk_appointments_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_appointments_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Appointment Services
CREATE TABLE `appointment_services` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `appointment_id` INT UNSIGNED NOT NULL,
    `service_id` INT UNSIGNED NOT NULL,
    `employee_id` INT UNSIGNED,
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `duration_minutes` INT UNSIGNED NOT NULL DEFAULT 30,
    `start_time` TIME,
    `end_time` TIME,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `aps_appointment_idx` (`appointment_id`),
    CONSTRAINT `fk_aps_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_aps_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_aps_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Appointment Reminders
CREATE TABLE `appointment_reminders` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `appointment_id` INT UNSIGNED NOT NULL,
    `type` ENUM('email','sms') NOT NULL,
    `sent_at` DATETIME,
    `status` ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
    `error_message` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `ar_appointment_idx` (`appointment_id`),
    CONSTRAINT `fk_ar_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Invoices
CREATE TABLE `invoices` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `customer_id` INT UNSIGNED NOT NULL,
    `invoice_number` VARCHAR(50) NOT NULL UNIQUE,
    `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `tax_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `discount_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `grand_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `balance_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `payment_status` ENUM('pending','partial','paid','refunded','cancelled') NOT NULL DEFAULT 'pending',
    `notes` TEXT,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `invoices_number_idx` (`invoice_number`),
    INDEX `invoices_customer_idx` (`customer_id`),
    INDEX `invoices_status_idx` (`payment_status`),
    INDEX `invoices_branch_idx` (`branch_id`),
    INDEX `invoices_date_idx` (`created_at`),
    CONSTRAINT `fk_invoices_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_invoices_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Invoice Items
CREATE TABLE `invoice_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `invoice_id` INT UNSIGNED NOT NULL,
    `reference_type` ENUM('service','product','package') NOT NULL,
    `reference_id` INT UNSIGNED,
    `item_name` VARCHAR(200) NOT NULL,
    `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
    `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `discount_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `tax_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `total_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `ii_invoice_idx` (`invoice_id`),
    CONSTRAINT `fk_ii_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Invoice Payments
CREATE TABLE `invoice_payments` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `invoice_id` INT UNSIGNED NOT NULL,
    `payment_method` ENUM('cash','card','bank_transfer','gift_card','advance','membership') NOT NULL DEFAULT 'cash',
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `reference_number` VARCHAR(100),
    `notes` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `ip_invoice_idx` (`invoice_id`),
    CONSTRAINT `fk_ip_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Coupons
CREATE TABLE `coupons` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) NOT NULL UNIQUE,
    `discount_type` ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage',
    `discount_value` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `min_purchase` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `max_discount` DECIMAL(10,2) DEFAULT NULL,
    `max_uses` INT UNSIGNED DEFAULT NULL,
    `used_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `valid_from` DATETIME,
    `valid_to` DATETIME,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `coupons_code_idx` (`code`)
) ENGINE=InnoDB;

-- Invoice-Coupon Junction
CREATE TABLE `invoice_coupons` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `invoice_id` INT UNSIGNED NOT NULL,
    `coupon_id` INT UNSIGNED NOT NULL,
    `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_ic_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ic_coupon` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Refunds
CREATE TABLE `refunds` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `invoice_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `reason` TEXT NOT NULL,
    `refund_method` ENUM('cash','card','bank_transfer') DEFAULT 'cash',
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `refunds_invoice_idx` (`invoice_id`),
    CONSTRAINT `fk_refunds_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 6. EMPLOYEES & HR
-- ============================================================

-- Employees
CREATE TABLE `employees` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `user_id` INT UNSIGNED NULL,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100),
    `phone` VARCHAR(20),
    `mobile` VARCHAR(20),
    `date_of_birth` DATE,
    `gender` ENUM('male','female','other') NULL,
    `address` TEXT,
    `city` VARCHAR(100),
    `state` VARCHAR(100),
    `zip` VARCHAR(20),
    `photo` VARCHAR(255),
    `job_title` VARCHAR(100),
    `hire_date` DATE,
    `salary_type` ENUM('fixed','hourly','commission_only') NOT NULL DEFAULT 'fixed',
    `salary_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `hourly_rate` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `commission_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `target_amount` DECIMAL(12,2) DEFAULT NULL,
    `emergency_contact` VARCHAR(100),
    `emergency_phone` VARCHAR(20),
    `notes` TEXT,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `employees_name_idx` (`first_name`, `last_name`),
    INDEX `employees_branch_idx` (`branch_id`),
    INDEX `employees_status_idx` (`is_active`),
    CONSTRAINT `fk_employees_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_employees_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Employee Documents
CREATE TABLE `employee_documents` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `document_type` VARCHAR(50) NOT NULL,
    `file_name` VARCHAR(255) NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `uploaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `ed_employee_idx` (`employee_id`),
    CONSTRAINT `fk_ed_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Employee Schedule
CREATE TABLE `employee_schedule` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `day_of_week` TINYINT UNSIGNED NOT NULL COMMENT '1=Monday,7=Sunday',
    `start_time` TIME NOT NULL,
    `end_time` TIME NOT NULL,
    `is_working` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_employee_day` (`employee_id`, `day_of_week`),
    CONSTRAINT `fk_esch_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Employee Time Off
CREATE TABLE `employee_time_off` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `type` ENUM('vacation','sick','personal','other') NOT NULL DEFAULT 'vacation',
    `reason` TEXT,
    `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `approved_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `eto_employee_idx` (`employee_id`),
    CONSTRAINT `fk_eto_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Employee Attendance
CREATE TABLE `employee_attendance` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `date` DATE NOT NULL,
    `check_in` DATETIME,
    `check_out` DATETIME,
    `status` ENUM('present','late','absent','half_day','holiday') NOT NULL DEFAULT 'present',
    `notes` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_employee_date` (`employee_id`, `date`),
    INDEX `ea_date_idx` (`date`),
    CONSTRAINT `fk_ea_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Employee Commissions
CREATE TABLE `employee_commissions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `invoice_id` INT UNSIGNED NOT NULL,
    `service_id` INT UNSIGNED,
    `commission_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `calculated_date` DATE NOT NULL,
    `paid_status` ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
    `paid_date` DATE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `ec_employee_idx` (`employee_id`),
    INDEX `ec_invoice_idx` (`invoice_id`),
    INDEX `ec_status_idx` (`paid_status`),
    CONSTRAINT `fk_ec_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ec_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Employee Salary
CREATE TABLE `employee_salary` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `salary_date` DATE NOT NULL,
    `basic_salary` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `commission_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `bonus` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `deductions` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `net_salary` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `payment_status` ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
    `payment_date` DATE,
    `paid_by` INT UNSIGNED,
    `notes` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `es_employee_idx` (`employee_id`),
    INDEX `es_date_idx` (`salary_date`),
    CONSTRAINT `fk_es_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Employee Performance Reviews
CREATE TABLE `employee_performance` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `review_date` DATE NOT NULL,
    `rating` TINYINT UNSIGNED NOT NULL COMMENT '1-5',
    `feedback` TEXT,
    `strengths` TEXT,
    `areas_for_improvement` TEXT,
    `reviewer_id` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `ep_employee_idx` (`employee_id`),
    CONSTRAINT `fk_ep_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 7. FINANCIAL
-- ============================================================

-- Expense Categories
CREATE TABLE `expense_categories` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `description` TEXT,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Expenses
CREATE TABLE `expenses` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `category_id` INT UNSIGNED,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `description` TEXT NOT NULL,
    `date` DATE NOT NULL,
    `payment_method` ENUM('cash','card','bank_transfer') DEFAULT 'cash',
    `reference_number` VARCHAR(100),
    `receipt_path` VARCHAR(255),
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `expenses_date_idx` (`date`),
    INDEX `expenses_category_idx` (`category_id`),
    INDEX `expenses_branch_idx` (`branch_id`),
    CONSTRAINT `fk_expenses_category` FOREIGN KEY (`category_id`) REFERENCES `expense_categories` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_expenses_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Income Categories
CREATE TABLE `income_categories` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `description` TEXT,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Income
CREATE TABLE `income` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `category_id` INT UNSIGNED,
    `invoice_id` INT UNSIGNED NULL,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `description` TEXT,
    `date` DATE NOT NULL,
    `payment_method` ENUM('cash','card','bank_transfer') DEFAULT 'cash',
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `income_date_idx` (`date`),
    INDEX `income_category_idx` (`category_id`),
    INDEX `income_branch_idx` (`branch_id`),
    CONSTRAINT `fk_income_category` FOREIGN KEY (`category_id`) REFERENCES `income_categories` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_income_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Cash Book
CREATE TABLE `cash_book` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `transaction_date` DATE NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `type` ENUM('inflow','outflow') NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `reference_type` VARCHAR(50),
    `reference_id` INT UNSIGNED,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `cb_date_idx` (`transaction_date`),
    INDEX `cb_branch_idx` (`branch_id`),
    CONSTRAINT `fk_cb_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Bank Accounts
CREATE TABLE `bank_accounts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `bank_name` VARCHAR(100) NOT NULL,
    `account_name` VARCHAR(100) NOT NULL,
    `account_number` VARCHAR(50) NOT NULL,
    `opening_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `current_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `ba_branch_idx` (`branch_id`),
    CONSTRAINT `fk_ba_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Bank Transactions
CREATE TABLE `bank_transactions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `bank_account_id` INT UNSIGNED NOT NULL,
    `transaction_date` DATE NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `type` ENUM('inflow','outflow') NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `reference_type` VARCHAR(50),
    `reference_id` INT UNSIGNED,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `bt_account_idx` (`bank_account_id`),
    INDEX `bt_date_idx` (`transaction_date`),
    CONSTRAINT `fk_bt_account` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Petty Cash
CREATE TABLE `petty_cash` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED,
    `transaction_date` DATE NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `type` ENUM('inflow','outflow') NOT NULL DEFAULT 'outflow',
    `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `receipt_path` VARCHAR(255),
    `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `approved_by` INT UNSIGNED,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `pc_branch_idx` (`branch_id`),
    INDEX `pc_status_idx` (`status`),
    CONSTRAINT `fk_pc_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 8. MARKETING & COMMUNICATION
-- ============================================================

-- Email Templates
CREATE TABLE `email_templates` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `subject` VARCHAR(255) NOT NULL,
    `body_html` TEXT NOT NULL,
    `variables` TEXT COMMENT 'JSON array of variable names',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- SMS Templates
CREATE TABLE `sms_templates` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `message` TEXT NOT NULL,
    `variables` TEXT COMMENT 'JSON array of variable names',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Campaigns
CREATE TABLE `campaigns` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(200) NOT NULL,
    `type` ENUM('email','sms','both') NOT NULL DEFAULT 'email',
    `template_id` INT UNSIGNED,
    `audience_criteria` TEXT COMMENT 'JSON of filter criteria',
    `scheduled_date` DATETIME,
    `sent_date` DATETIME,
    `status` ENUM('draft','scheduled','sending','sent','cancelled') NOT NULL DEFAULT 'draft',
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `camp_status_idx` (`status`)
) ENGINE=InnoDB;

-- Campaign Logs
CREATE TABLE `campaign_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `campaign_id` INT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED,
    `type` ENUM('email','sms') NOT NULL,
    `sent_at` DATETIME,
    `status` ENUM('pending','sent','failed','opened','clicked') DEFAULT 'pending',
    `error_message` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `cl_campaign_idx` (`campaign_id`),
    CONSTRAINT `fk_cl_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Notifications
CREATE TABLE `notifications` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED,
    `type` VARCHAR(50) NOT NULL DEFAULT 'info',
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT,
    `reference_type` VARCHAR(50),
    `reference_id` INT UNSIGNED,
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `read_at` DATETIME,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `notif_user_idx` (`user_id`),
    INDEX `notif_read_idx` (`is_read`),
    CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Notification Templates
CREATE TABLE `notification_templates` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL UNIQUE,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT,
    `type` VARCHAR(50) DEFAULT 'info',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Email Queue
CREATE TABLE `email_queue` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `to_email` VARCHAR(100) NOT NULL,
    `to_name` VARCHAR(100),
    `subject` VARCHAR(255) NOT NULL,
    `body_html` TEXT NOT NULL,
    `attachments` TEXT COMMENT 'JSON array of file paths',
    `priority` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `status` ENUM('pending','sending','sent','failed') NOT NULL DEFAULT 'pending',
    `error_message` TEXT,
    `sent_at` DATETIME,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `eq_status_idx` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- 9. SYSTEM & AUDIT
-- ============================================================

-- Settings
CREATE TABLE `settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `branch_id` INT UNSIGNED NULL,
    `key` VARCHAR(100) NOT NULL,
    `value` TEXT,
    `group_name` VARCHAR(50) DEFAULT 'general',
    `type` ENUM('text','number','boolean','select','textarea','image') DEFAULT 'text',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_branch_setting` (`branch_id`, `key`),
    INDEX `settings_group_idx` (`group_name`)
) ENGINE=InnoDB;

-- Audit Logs
CREATE TABLE `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED,
    `action` VARCHAR(50) NOT NULL,
    `table_name` VARCHAR(50) NOT NULL,
    `record_id` INT UNSIGNED,
    `old_values` JSON,
    `new_values` JSON,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `audit_user_idx` (`user_id`),
    INDEX `audit_table_idx` (`table_name`),
    INDEX `audit_action_idx` (`action`),
    INDEX `audit_created_idx` (`created_at`)
) ENGINE=InnoDB;

-- Activity Logs
CREATE TABLE `activity_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED,
    `action` VARCHAR(50) NOT NULL,
    `description` TEXT,
    `ip_address` VARCHAR(45),
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `al_user_idx` (`user_id`),
    INDEX `al_action_idx` (`action`),
    INDEX `al_created_idx` (`created_at`)
) ENGINE=InnoDB;

-- Backups
CREATE TABLE `backups` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `filename` VARCHAR(255) NOT NULL,
    `filepath` VARCHAR(255) NOT NULL,
    `size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `type` ENUM('full','database','files') NOT NULL DEFAULT 'full',
    `notes` TEXT,
    `created_by` INT UNSIGNED,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `backups_created_idx` (`created_at`)
) ENGINE=InnoDB;

SET foreign_key_checks = 1;
