-- ============================================================
-- Locker Management System — Database Schema
-- Target: MySQL 8.0+ / MariaDB 10.6+
-- Charset: utf8mb4 (supports Bengali + emoji)
-- Engine: InnoDB
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;
SET time_zone = '+06:00';

-- ============================================================
-- IDENTITY & ACCESS
-- ============================================================

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    email_verified_at TIMESTAMP NULL DEFAULT NULL,
    password VARCHAR(255) NOT NULL,
    two_factor_secret TEXT DEFAULT NULL,
    two_factor_recovery_codes TEXT DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    last_login_ip VARCHAR(45) DEFAULT NULL,
    remember_token VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_users_email (email),
    KEY idx_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Spatie laravel-permission tables (standard package schema)
CREATE TABLE roles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    guard_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_roles_name_guard (name, guard_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    guard_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_permissions_name_guard (name, guard_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE model_has_roles (
    role_id BIGINT UNSIGNED NOT NULL,
    model_type VARCHAR(255) NOT NULL,
    model_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, model_id, model_type),
    KEY idx_model_has_roles_model (model_id, model_type),
    CONSTRAINT fk_mhr_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE model_has_permissions (
    permission_id BIGINT UNSIGNED NOT NULL,
    model_type VARCHAR(255) NOT NULL,
    model_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (permission_id, model_id, model_type),
    KEY idx_model_has_permissions_model (model_id, model_type),
    CONSTRAINT fk_mhp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_has_permissions (
    permission_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (permission_id, role_id),
    CONSTRAINT fk_rhp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    CONSTRAINT fk_rhp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- REFERENCE / CONFIGURATION
-- ============================================================

CREATE TABLE branches (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address TEXT DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE zones (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    gender_restriction ENUM('male','female','any') NOT NULL DEFAULT 'any',
    description TEXT DEFAULT NULL,
    display_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_zones_code (code),
    KEY idx_zones_branch (branch_id),
    CONSTRAINT fk_zones_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE locker_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) NOT NULL,
    description TEXT DEFAULT NULL,
    base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    display_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_locker_categories_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pricing_plans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT UNSIGNED DEFAULT NULL,
    zone_id BIGINT UNSIGNED DEFAULT NULL,
    name VARCHAR(100) NOT NULL,
    duration_days INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_pricing_plans_combo (category_id, zone_id, duration_days),
    KEY idx_pricing_plans_active (is_active),
    CONSTRAINT fk_pp_category FOREIGN KEY (category_id) REFERENCES locker_categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_pp_zone FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_methods (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL,
    type ENUM('manual','gateway') NOT NULL,
    config JSON DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    display_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_payment_methods_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_templates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    channel ENUM('email','sms','whatsapp','inapp') NOT NULL,
    language ENUM('en','bn') NOT NULL DEFAULT 'bn',
    subject VARCHAR(500) DEFAULT NULL,
    body TEXT NOT NULL,
    variables JSON DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_notification_templates (code, channel, language)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL,
    `value` TEXT DEFAULT NULL,
    `type` ENUM('string','number','boolean','json','encrypted') NOT NULL DEFAULT 'string',
    `group` VARCHAR(50) NOT NULL DEFAULT 'general',
    description TEXT DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_settings_key (`key`),
    KEY idx_settings_group (`group`),
    CONSTRAINT fk_settings_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tags (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    color VARCHAR(20) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_tags_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CLIENT MANAGEMENT
-- ============================================================

CREATE TABLE clients (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_code VARCHAR(20) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    gender ENUM('male','female','other') NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255) DEFAULT NULL,
    nid_or_passport VARCHAR(50) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    emergency_contact_name VARCHAR(255) DEFAULT NULL,
    emergency_contact_phone VARCHAR(20) DEFAULT NULL,
    photo_path VARCHAR(500) DEFAULT NULL,
    signature_path VARCHAR(500) DEFAULT NULL,
    preferred_language ENUM('en','bn') NOT NULL DEFAULT 'bn',
    status ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
    ban_reason TEXT DEFAULT NULL,
    referral_source VARCHAR(255) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    registered_by BIGINT UNSIGNED NOT NULL,
    registered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_clients_code (client_code),
    KEY idx_clients_phone (phone),
    KEY idx_clients_email (email),
    KEY idx_clients_nid (nid_or_passport),
    KEY idx_clients_status (status),
    KEY idx_clients_gender (gender),
    FULLTEXT KEY ft_clients_name (full_name),
    CONSTRAINT fk_clients_registered_by FOREIGN KEY (registered_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED NOT NULL,
    document_type ENUM('nid','passport','photo','signature','other') NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    original_filename VARCHAR(255) DEFAULT NULL,
    mime_type VARCHAR(100) DEFAULT NULL,
    file_size INT DEFAULT NULL,
    uploaded_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_client_docs_client (client_id, document_type),
    CONSTRAINT fk_cd_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_cd_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_tag (
    client_id BIGINT UNSIGNED NOT NULL,
    tag_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (client_id, tag_id),
    KEY idx_ct_tag (tag_id),
    CONSTRAINT fk_ct_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_ct_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- LOCKER INVENTORY
-- ============================================================

CREATE TABLE lockers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    zone_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    locker_number VARCHAR(50) NOT NULL,
    floor VARCHAR(50) DEFAULT NULL,
    position VARCHAR(50) DEFAULT NULL,
    status ENUM('available','assigned','reserved','maintenance','cleaning','out_of_service') NOT NULL DEFAULT 'available',
    notes TEXT DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_lockers_number (locker_number),
    KEY idx_lockers_zone_status (zone_id, status),
    KEY idx_lockers_category_status (category_id, status),
    KEY idx_lockers_status (status),
    CONSTRAINT fk_lockers_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
    CONSTRAINT fk_lockers_zone FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE RESTRICT,
    CONSTRAINT fk_lockers_category FOREIGN KEY (category_id) REFERENCES locker_categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `keys` (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    locker_id BIGINT UNSIGNED NOT NULL,
    key_code VARCHAR(50) NOT NULL,
    status ENUM('available','issued','lost','damaged','replaced','retired') NOT NULL DEFAULT 'available',
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_keys_locker_status (locker_id, status),
    KEY idx_keys_code (key_code),
    CONSTRAINT fk_keys_locker FOREIGN KEY (locker_id) REFERENCES lockers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE maintenance_tickets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ticket_number VARCHAR(50) NOT NULL,
    locker_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    status ENUM('open','in_progress','resolved','closed','cancelled') NOT NULL DEFAULT 'open',
    reported_by BIGINT UNSIGNED NOT NULL,
    assigned_to BIGINT UNSIGNED DEFAULT NULL,
    resolved_at TIMESTAMP NULL DEFAULT NULL,
    resolution_notes TEXT DEFAULT NULL,
    attachment_path VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_mt_number (ticket_number),
    KEY idx_mt_locker_status (locker_id, status),
    KEY idx_mt_status_priority (status, priority),
    CONSTRAINT fk_mt_locker FOREIGN KEY (locker_id) REFERENCES lockers(id) ON DELETE CASCADE,
    CONSTRAINT fk_mt_reporter FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_mt_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- RENTALS & BILLING
-- ============================================================

CREATE TABLE rentals (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rental_code VARCHAR(20) NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    locker_id BIGINT UNSIGNED NOT NULL,
    key_id BIGINT UNSIGNED DEFAULT NULL,
    pricing_plan_id BIGINT UNSIGNED NOT NULL,
    start_date DATE NOT NULL,
    expiry_date DATE NOT NULL,
    grace_until DATE DEFAULT NULL,
    rental_fee DECIMAL(10,2) NOT NULL,
    deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,
    paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    due_amount DECIMAL(10,2) GENERATED ALWAYS AS (total_amount - paid_amount) STORED,
    payment_status ENUM('paid','partial','due','overdue','refunded') NOT NULL DEFAULT 'due',
    status ENUM('reserved','active','expired','cancelled','completed') NOT NULL DEFAULT 'reserved',
    key_issued_at TIMESTAMP NULL DEFAULT NULL,
    key_returned_at TIMESTAMP NULL DEFAULT NULL,
    deposit_refunded_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    cancellation_reason TEXT DEFAULT NULL,
    assigned_by BIGINT UNSIGNED NOT NULL,
    closed_by BIGINT UNSIGNED DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_rentals_code (rental_code),
    KEY idx_rentals_client_status (client_id, status),
    KEY idx_rentals_locker_status (locker_id, status),
    KEY idx_rentals_expiry (expiry_date, status),
    KEY idx_rentals_payment (payment_status),
    KEY idx_rentals_status_expiry (status, expiry_date),
    CONSTRAINT fk_rentals_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
    CONSTRAINT fk_rentals_locker FOREIGN KEY (locker_id) REFERENCES lockers(id) ON DELETE RESTRICT,
    CONSTRAINT fk_rentals_key FOREIGN KEY (key_id) REFERENCES `keys`(id) ON DELETE SET NULL,
    CONSTRAINT fk_rentals_pricing FOREIGN KEY (pricing_plan_id) REFERENCES pricing_plans(id) ON DELETE RESTRICT,
    CONSTRAINT fk_rentals_assigned_by FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_rentals_closed_by FOREIGN KEY (closed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE renewals (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rental_id BIGINT UNSIGNED NOT NULL,
    previous_expiry_date DATE NOT NULL,
    new_expiry_date DATE NOT NULL,
    duration_days INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_id BIGINT UNSIGNED DEFAULT NULL,
    processed_by BIGINT UNSIGNED NOT NULL,
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_renewals_rental_date (rental_id, created_at),
    CONSTRAINT fk_renewals_rental FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE CASCADE,
    CONSTRAINT fk_renewals_user FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    rental_id BIGINT UNSIGNED DEFAULT NULL,
    issued_date DATE NOT NULL,
    due_date DATE DEFAULT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,
    paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status ENUM('draft','issued','paid','partial','overdue','cancelled','refunded') NOT NULL DEFAULT 'draft',
    pdf_path VARCHAR(500) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_invoices_number (invoice_number),
    KEY idx_invoices_client_date (client_id, issued_date),
    KEY idx_invoices_status_due (status, due_date),
    KEY idx_invoices_rental (rental_id),
    CONSTRAINT fk_invoices_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
    CONSTRAINT fk_invoices_rental FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE SET NULL,
    CONSTRAINT fk_invoices_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoice_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(500) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_ii_invoice (invoice_id),
    CONSTRAINT fk_ii_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    payment_code VARCHAR(30) NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    rental_id BIGINT UNSIGNED DEFAULT NULL,
    invoice_id BIGINT UNSIGNED DEFAULT NULL,
    payment_method_id BIGINT UNSIGNED NOT NULL,
    type ENUM('rental','renewal','deposit','lost_key_fee','other','refund') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','completed','failed','refunded','cancelled') NOT NULL DEFAULT 'pending',
    transaction_reference VARCHAR(255) DEFAULT NULL,
    gateway_response JSON DEFAULT NULL,
    paid_at TIMESTAMP NULL DEFAULT NULL,
    collected_by BIGINT UNSIGNED DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_payments_code (payment_code),
    KEY idx_payments_client_date (client_id, paid_at),
    KEY idx_payments_rental_status (rental_id, status),
    KEY idx_payments_status_date (status, paid_at),
    KEY idx_payments_txn (transaction_reference),
    KEY idx_payments_invoice (invoice_id),
    CONSTRAINT fk_payments_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
    CONSTRAINT fk_payments_rental FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE SET NULL,
    CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
    CONSTRAINT fk_payments_method FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE RESTRICT,
    CONSTRAINT fk_payments_collector FOREIGN KEY (collected_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add the FK from renewals.payment_id to payments now that payments exists
ALTER TABLE renewals
    ADD CONSTRAINT fk_renewals_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL;

CREATE TABLE deposits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rental_id BIGINT UNSIGNED NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_id BIGINT UNSIGNED DEFAULT NULL,
    refunded_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    refunded_at TIMESTAMP NULL DEFAULT NULL,
    refund_payment_id BIGINT UNSIGNED DEFAULT NULL,
    deducted_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    deduction_reason TEXT DEFAULT NULL,
    status ENUM('held','refunded','partially_refunded','forfeited') NOT NULL DEFAULT 'held',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_deposits_rental (rental_id),
    KEY idx_deposits_client_status (client_id, status),
    CONSTRAINT fk_deposits_rental FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE RESTRICT,
    CONSTRAINT fk_deposits_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
    CONSTRAINT fk_deposits_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
    CONSTRAINT fk_deposits_refund_payment FOREIGN KEY (refund_payment_id) REFERENCES payments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE discount_coupons (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    discount_type ENUM('percent','fixed') NOT NULL,
    discount_value DECIMAL(10,2) NOT NULL,
    min_amount DECIMAL(10,2) DEFAULT NULL,
    max_redemptions INT DEFAULT NULL,
    redemptions_count INT NOT NULL DEFAULT 0,
    valid_from DATE DEFAULT NULL,
    valid_to DATE DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_coupons_code (code),
    KEY idx_coupons_active_dates (is_active, valid_from, valid_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE coupon_redemptions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    coupon_id BIGINT UNSIGNED NOT NULL,
    rental_id BIGINT UNSIGNED NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL,
    redeemed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_cr_coupon_date (coupon_id, redeemed_at),
    KEY idx_cr_client (client_id),
    KEY idx_cr_rental (rental_id),
    CONSTRAINT fk_cr_coupon FOREIGN KEY (coupon_id) REFERENCES discount_coupons(id) ON DELETE CASCADE,
    CONSTRAINT fk_cr_rental FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE CASCADE,
    CONSTRAINT fk_cr_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lost_key_incidents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rental_id BIGINT UNSIGNED NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    key_id BIGINT UNSIGNED NOT NULL,
    replacement_key_id BIGINT UNSIGNED DEFAULT NULL,
    replacement_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    deducted_from_deposit TINYINT(1) NOT NULL DEFAULT 0,
    fee_payment_id BIGINT UNSIGNED DEFAULT NULL,
    status ENUM('reported','replaced','closed') NOT NULL DEFAULT 'reported',
    reported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    reported_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_lki_client_status (client_id, status),
    KEY idx_lki_rental (rental_id),
    CONSTRAINT fk_lki_rental FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE CASCADE,
    CONSTRAINT fk_lki_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
    CONSTRAINT fk_lki_key FOREIGN KEY (key_id) REFERENCES `keys`(id) ON DELETE RESTRICT,
    CONSTRAINT fk_lki_replacement FOREIGN KEY (replacement_key_id) REFERENCES `keys`(id) ON DELETE SET NULL,
    CONSTRAINT fk_lki_payment FOREIGN KEY (fee_payment_id) REFERENCES payments(id) ON DELETE SET NULL,
    CONSTRAINT fk_lki_user FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lost_found_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    locker_id BIGINT UNSIGNED DEFAULT NULL,
    rental_id BIGINT UNSIGNED DEFAULT NULL,
    client_id BIGINT UNSIGNED DEFAULT NULL,
    item_description TEXT NOT NULL,
    photo_path VARCHAR(500) DEFAULT NULL,
    found_date DATE NOT NULL,
    storage_location VARCHAR(255) DEFAULT NULL,
    status ENUM('stored','claimed','disposed') NOT NULL DEFAULT 'stored',
    claimed_by VARCHAR(255) DEFAULT NULL,
    claimed_date DATE DEFAULT NULL,
    disposed_date DATE DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    recorded_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_lfi_client (client_id),
    KEY idx_lfi_status_date (status, found_date),
    KEY idx_lfi_locker (locker_id),
    CONSTRAINT fk_lfi_locker FOREIGN KEY (locker_id) REFERENCES lockers(id) ON DELETE SET NULL,
    CONSTRAINT fk_lfi_rental FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE SET NULL,
    CONSTRAINT fk_lfi_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    CONSTRAINT fk_lfi_user FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- NOTIFICATIONS & AUDIT
-- ============================================================

CREATE TABLE notifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED DEFAULT NULL,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    template_id BIGINT UNSIGNED DEFAULT NULL,
    channel ENUM('email','sms','whatsapp','inapp') NOT NULL,
    recipient VARCHAR(255) NOT NULL,
    subject VARCHAR(500) DEFAULT NULL,
    body TEXT NOT NULL,
    status ENUM('queued','sending','sent','delivered','failed','read') NOT NULL DEFAULT 'queued',
    failure_reason TEXT DEFAULT NULL,
    external_message_id VARCHAR(255) DEFAULT NULL,
    cost DECIMAL(10,4) DEFAULT NULL,
    related_type VARCHAR(100) DEFAULT NULL,
    related_id BIGINT UNSIGNED DEFAULT NULL,
    sent_at TIMESTAMP NULL DEFAULT NULL,
    delivered_at TIMESTAMP NULL DEFAULT NULL,
    read_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_notif_client_date (client_id, created_at),
    KEY idx_notif_status_date (status, created_at),
    KEY idx_notif_related (related_type, related_id),
    KEY idx_notif_channel_status (channel, status),
    KEY idx_notif_user (user_id),
    CONSTRAINT fk_notif_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_notif_template FOREIGN KEY (template_id) REFERENCES notification_templates(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Spatie activity_log
CREATE TABLE activity_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    log_name VARCHAR(255) DEFAULT NULL,
    description TEXT NOT NULL,
    subject_type VARCHAR(255) DEFAULT NULL,
    subject_id BIGINT UNSIGNED DEFAULT NULL,
    causer_type VARCHAR(255) DEFAULT NULL,
    causer_id BIGINT UNSIGNED DEFAULT NULL,
    properties JSON DEFAULT NULL,
    event VARCHAR(255) DEFAULT NULL,
    batch_uuid CHAR(36) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_al_subject (subject_type, subject_id),
    KEY idx_al_causer (causer_type, causer_id),
    KEY idx_al_log_name (log_name),
    KEY idx_al_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE backups (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT DEFAULT NULL,
    type ENUM('manual','scheduled') NOT NULL,
    status ENUM('in_progress','completed','failed') NOT NULL,
    error_message TEXT DEFAULT NULL,
    started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_backups_status_date (status, started_at),
    CONSTRAINT fk_backups_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- SEED DATA (minimum to bootstrap the system)
-- ============================================================

INSERT INTO branches (name, address, phone, email, is_active, created_at, updated_at)
VALUES ('Main Branch', 'TBD', NULL, NULL, 1, NOW(), NOW());

INSERT INTO zones (branch_id, name, code, gender_restriction, display_order, is_active, created_at, updated_at) VALUES
    (1, 'Men''s Section',    'MEN',    'male',   1, 1, NOW(), NOW()),
    (1, 'Women''s Section',  'WOMEN',  'female', 2, 1, NOW(), NOW()),
    (1, 'Family Section',    'FAMILY', 'any',    3, 1, NOW(), NOW()),
    (1, 'VIP Section',       'VIP',    'any',    4, 1, NOW(), NOW());

INSERT INTO locker_categories (name, code, base_price, display_order, is_active, created_at, updated_at) VALUES
    ('Small',  'S', 500.00,  1, 1, NOW(), NOW()),
    ('Medium', 'M', 800.00,  2, 1, NOW(), NOW()),
    ('Large',  'L', 1200.00, 3, 1, NOW(), NOW());

INSERT INTO payment_methods (name, code, type, is_active, display_order, created_at, updated_at) VALUES
    ('Cash',       'cash',       'manual',  1, 1, NOW(), NOW()),
    ('bKash',      'bkash',      'gateway', 1, 2, NOW(), NOW()),
    ('Nagad',      'nagad',      'gateway', 1, 3, NOW(), NOW()),
    ('SSLCommerz', 'sslcommerz', 'gateway', 1, 4, NOW(), NOW());

INSERT INTO settings (`key`, `value`, `type`, `group`, description, created_at, updated_at) VALUES
    ('company.name',          'My Gym',      'string',  'company',   'Gym name on invoices',                 NOW(), NOW()),
    ('company.currency',      'BDT',         'string',  'company',   'Currency code',                        NOW(), NOW()),
    ('company.currency_symbol','৳',          'string',  'company',   'Currency symbol on invoices',          NOW(), NOW()),
    ('company.timezone',      'Asia/Dhaka',  'string',  'company',   'System timezone',                      NOW(), NOW()),
    ('company.tax_rate',      '0',           'number',  'company',   'Default tax rate %',                   NOW(), NOW()),
    ('reminders.days_before', '[7,3,1]',     'json',    'reminders', 'Days before expiry to send reminders', NOW(), NOW()),
    ('reminders.overdue_days','[3,7]',       'json',    'reminders', 'Days after expiry to send reminders',  NOW(), NOW()),
    ('rental.grace_days',     '3',           'number',  'rental',    'Grace period after expiry',            NOW(), NOW()),
    ('rental.lost_key_fee',   '500',         'number',  'rental',    'Default lost-key replacement fee',     NOW(), NOW()),
    ('invoice.prefix',        'INV',         'string',  'invoice',   'Invoice number prefix',                NOW(), NOW()),
    ('invoice.starting_number','1',          'number',  'invoice',   'Starting invoice number',              NOW(), NOW());

INSERT INTO roles (name, guard_name, created_at, updated_at) VALUES
    ('super_admin', 'web', NOW(), NOW()),
    ('manager',     'web', NOW(), NOW()),
    ('front_desk',  'web', NOW(), NOW()),
    ('accountant',  'web', NOW(), NOW());