-- =============================================================================
-- Licentia Database Schema (Consolidated)
-- MySQL 8.0
-- Generated from Laravel migration files with inline constraints.
-- =============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- =============================================================================
-- SUPPORT MODULE - Framework / Infrastructure Tables
-- =============================================================================

-- Password reset tokens (framework)
CREATE TABLE `password_reset_tokens` (
    `email` VARCHAR(255) NOT NULL,
    `token` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sessions (framework)
CREATE TABLE `sessions` (
    `id` VARCHAR(255) NOT NULL,
    `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `ip_address` VARCHAR(45) NULL DEFAULT NULL,
    `user_agent` TEXT NULL DEFAULT NULL,
    `payload` LONGTEXT NOT NULL,
    `last_activity` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX (`user_id`),
    INDEX (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cache (framework)
CREATE TABLE `cache` (
    `key` VARCHAR(255) NOT NULL,
    `value` MEDIUMTEXT NOT NULL,
    `expiration` INT NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cache locks (framework)
CREATE TABLE `cache_locks` (
    `key` VARCHAR(255) NOT NULL,
    `owner` VARCHAR(255) NOT NULL,
    `expiration` INT NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Queue jobs (framework)
CREATE TABLE `queue_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue` VARCHAR(255) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `attempts` TINYINT UNSIGNED NOT NULL,
    `reserved_at` INT UNSIGNED NULL DEFAULT NULL,
    `available_at` INT UNSIGNED NOT NULL,
    `created_at` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    INDEX (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Queue job batches (framework)
CREATE TABLE `queue_job_batches` (
    `id` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `total_jobs` INT NOT NULL,
    `pending_jobs` INT NOT NULL,
    `failed_jobs` INT NOT NULL,
    `failed_job_ids` LONGTEXT NOT NULL,
    `options` MEDIUMTEXT NULL DEFAULT NULL,
    `cancelled_at` INT NULL DEFAULT NULL,
    `created_at` INT NOT NULL,
    `finished_at` INT NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Queue failed jobs (framework)
CREATE TABLE `queue_failed_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(255) NOT NULL,
    `connection` TEXT NOT NULL,
    `queue` TEXT NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `exception` LONGTEXT NOT NULL,
    `failed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notifications (framework)
CREATE TABLE `notifications` (
    `id` CHAR(36) NOT NULL,
    `type` VARCHAR(255) NOT NULL,
    `notifiable_type` VARCHAR(255) NOT NULL,
    `notifiable_id` BIGINT UNSIGNED NOT NULL,
    `data` TEXT NOT NULL,
    `read_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX (`notifiable_type`, `notifiable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Entity events (sp_entity_events) - audit trail
CREATE TABLE `sp_entity_events` (
    `entity_event_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `entity_type` VARCHAR(255) NOT NULL,
    `entity_id` BIGINT UNSIGNED NOT NULL,
    `causer_type` VARCHAR(255) NULL DEFAULT NULL,
    `causer_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `properties` JSON NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`entity_event_id`),
    INDEX (`entity_type`, `entity_id`),
    INDEX (`causer_type`, `causer_id`),
    INDEX (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Navigations (sp_navigations)
CREATE TABLE `sp_navigations` (
    `navigation_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `handle` VARCHAR(255) NOT NULL,
    `items` LONGTEXT NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`navigation_id`),
    UNIQUE KEY (`handle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Uploads (sp_uploads)
CREATE TABLE `sp_uploads` (
    `upload_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uploader_type` VARCHAR(255) NOT NULL,
    `uploader_id` BIGINT UNSIGNED NOT NULL,
    `recipient_type` VARCHAR(255) NOT NULL,
    `recipient_id` BIGINT UNSIGNED NOT NULL,
    `recipient_role` VARCHAR(255) NULL DEFAULT NULL,
    `upload_type` VARCHAR(255) NOT NULL,
    `mime_type` VARCHAR(30) NOT NULL,
    `url_key` VARCHAR(30) NOT NULL,
    `upload_description` VARCHAR(255) NULL DEFAULT NULL,
    `upload_name_original` VARCHAR(100) NOT NULL,
    `upload_name_new` VARCHAR(100) NOT NULL,
    `upload_path` VARCHAR(255) NOT NULL,
    `disk` VARCHAR(255) NOT NULL DEFAULT 'public',
    `upload_size` INT NOT NULL,
    `checksum` VARCHAR(64) NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`upload_id`),
    UNIQUE KEY (`url_key`),
    INDEX (`uploader_type`, `uploader_id`),
    INDEX (`recipient_type`, `recipient_id`),
    INDEX (`recipient_role`),
    INDEX (`upload_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Upload details (sp_upload_details)
CREATE TABLE `sp_upload_details` (
    `upload_detail_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `upload_id` BIGINT UNSIGNED NOT NULL,
    `upload_value` TEXT NOT NULL,
    `upload_value_type` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`upload_detail_id`),
    CONSTRAINT `fk_upload_details_upload_id` FOREIGN KEY (`upload_id`)
        REFERENCES `sp_uploads` (`upload_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- USER MODULE (usr_)
-- =============================================================================

-- Contacts (usr_contacts)
CREATE TABLE `usr_contacts` (
    `contact_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(255) NOT NULL,
    `middle_name` VARCHAR(255) NULL DEFAULT NULL,
    `last_name` VARCHAR(255) NULL DEFAULT NULL,
    `date_of_birth` DATE NULL DEFAULT NULL,
    `id_number` VARCHAR(13) NULL DEFAULT NULL,
    `passport_number` VARCHAR(10) NULL DEFAULT NULL,
    `gender` CHAR(1) NULL DEFAULT NULL,
    PRIMARY KEY (`contact_id`),
    UNIQUE KEY `contacts_unique_id` (`id_number`),
    UNIQUE KEY `contacts_unique_passport` (`passport_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users (usr_users)
CREATE TABLE `usr_users` (
    `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `contact_id` BIGINT UNSIGNED NOT NULL,
    `username` VARCHAR(50) NULL DEFAULT NULL,
    `password` VARCHAR(255) NULL DEFAULT NULL,
    `type` VARCHAR(255) NOT NULL,
    `can_access` TINYINT(1) NOT NULL DEFAULT 1,
    `credentials_must_be_updated` TINYINT(1) NOT NULL DEFAULT 0,
    `settings_package` JSON NULL DEFAULT NULL,
    `email` VARCHAR(255) NULL DEFAULT NULL,
    `cell` VARCHAR(255) NULL DEFAULT NULL,
    `alt_cell` VARCHAR(255) NULL DEFAULT NULL,
    `alt_whatsapp` VARCHAR(255) NULL DEFAULT NULL,
    `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
    `cell_verified_at` TIMESTAMP NULL DEFAULT NULL,
    `alternative_verified_at` TIMESTAMP NULL DEFAULT NULL,
    `last_active` TIMESTAMP NULL DEFAULT NULL,
    `remember_token` VARCHAR(255) NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`user_id`),
    UNIQUE KEY (`contact_id`),
    UNIQUE KEY (`username`),
    UNIQUE KEY `user_unique_email` (`email`),
    UNIQUE KEY `user_unique_cell` (`cell`),
    UNIQUE KEY `user_alt_cell_unique` (`alt_cell`),
    UNIQUE KEY `user_alt_whatsapp_unique` (`alt_whatsapp`),
    CONSTRAINT `users_contact_id_fk` FOREIGN KEY (`contact_id`)
        REFERENCES `usr_contacts` (`contact_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `users_cell_not_equal_alt_cell`
        CHECK (`cell` != `alt_cell` OR `cell` IS NULL OR `alt_cell` IS NULL),
    CONSTRAINT `users_cell_not_equal_alt_whatsapp`
        CHECK (`cell` != `alt_whatsapp` OR `cell` IS NULL OR `alt_whatsapp` IS NULL),
    CONSTRAINT `users_alt_cell_not_equal_alt_whatsapp`
        CHECK (`alt_cell` != `alt_whatsapp` OR `alt_cell` IS NULL OR `alt_whatsapp` IS NULL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee profiles (usr_employee_profiles)
CREATE TABLE `usr_employee_profiles` (
    `user_id` BIGINT UNSIGNED NOT NULL,
    `employment_date` DATE NOT NULL DEFAULT (CURRENT_DATE),
    `leave_days` TINYINT NOT NULL DEFAULT 15,
    PRIMARY KEY (`user_id`),
    CONSTRAINT `employee_profiles_user_id_fk` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Client profiles (usr_client_profiles)
CREATE TABLE `usr_client_profiles` (
    `user_id` BIGINT UNSIGNED NOT NULL,
    `client_flag` TINYINT NOT NULL,
    `popia_signed_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`user_id`),
    CONSTRAINT `client_profiles_user_id_fk` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notes (usr_notes)
CREATE TABLE `usr_notes` (
    `note_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `user_full_name` VARCHAR(255) NULL DEFAULT NULL,
    `notable_type` VARCHAR(255) NOT NULL,
    `notable_id` BIGINT UNSIGNED NOT NULL,
    `note_title` VARCHAR(255) NOT NULL,
    `note_text` MEDIUMTEXT NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`note_id`),
    INDEX (`notable_type`, `notable_id`),
    CONSTRAINT `notes_ibfk_1` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- LOCATION MODULE (lc_)
-- =============================================================================

-- Provinces (lc_provinces)
CREATE TABLE `lc_provinces` (
    `province_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `display_name` VARCHAR(150) NOT NULL,
    `code` VARCHAR(10) NOT NULL,
    PRIMARY KEY (`province_id`),
    UNIQUE KEY `province_name` (`name`),
    UNIQUE KEY `province_display_name` (`display_name`),
    UNIQUE KEY `province_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Districts (lc_districts)
CREATE TABLE `lc_districts` (
    `district_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `province_id` BIGINT UNSIGNED NOT NULL,
    `district_name` VARCHAR(255) NOT NULL,
    `district_code` VARCHAR(10) NOT NULL,
    PRIMARY KEY (`district_id`),
    UNIQUE KEY `district_name` (`district_name`),
    UNIQUE KEY `district_code` (`district_code`),
    CONSTRAINT `districts_ibfk_1` FOREIGN KEY (`province_id`)
        REFERENCES `lc_provinces` (`province_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Municipalities (lc_municipalities)
CREATE TABLE `lc_municipalities` (
    `municipality_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `district_id` BIGINT UNSIGNED NOT NULL,
    `parent_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `municipality_name` VARCHAR(255) NOT NULL,
    `municipality_code` VARCHAR(10) NOT NULL,
    `municipality_type` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`municipality_id`),
    UNIQUE KEY `municipality_code` (`municipality_code`),
    CONSTRAINT `municipalities_ibfk_1` FOREIGN KEY (`parent_id`)
        REFERENCES `lc_municipalities` (`municipality_id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `municipalities_ibfk_2` FOREIGN KEY (`district_id`)
        REFERENCES `lc_districts` (`district_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Towns (lc_towns)
CREATE TABLE `lc_towns` (
    `town_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `municipality_id` BIGINT UNSIGNED NOT NULL,
    `town_name` VARCHAR(255) NOT NULL,
    `town_code` VARCHAR(10) NOT NULL,
    PRIMARY KEY (`town_id`),
    UNIQUE KEY `town_code` (`town_code`),
    CONSTRAINT `towns_ibfk_1` FOREIGN KEY (`municipality_id`)
        REFERENCES `lc_municipalities` (`municipality_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Addresses (lc_addresses)
CREATE TABLE `lc_addresses` (
    `address_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `town_id` BIGINT UNSIGNED NOT NULL,
    `address_line_1` VARCHAR(255) NOT NULL,
    `address_line_2` VARCHAR(255) NULL DEFAULT NULL,
    `suburb` VARCHAR(255) NULL DEFAULT NULL,
    `postal_code` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`address_id`),
    CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`town_id`)
        REFERENCES `lc_towns` (`town_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Entity addresses (lc_entity_addresses) - polymorphic pivot
CREATE TABLE `lc_entity_addresses` (
    `entity_address_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `address_id` BIGINT UNSIGNED NOT NULL,
    `addressable_type` VARCHAR(255) NOT NULL,
    `addressable_id` BIGINT UNSIGNED NOT NULL,
    `address_relationship` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`entity_address_id`),
    UNIQUE KEY `entity_address_unique` (`address_id`, `addressable_id`, `addressable_type`, `address_relationship`),
    INDEX (`addressable_type`, `addressable_id`),
    CONSTRAINT `entity_addresses_address_id_fk` FOREIGN KEY (`address_id`)
        REFERENCES `lc_addresses` (`address_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Franchises (lc_franchises)
CREATE TABLE `lc_franchises` (
    `franchise_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `province_id` BIGINT UNSIGNED NOT NULL,
    `head_franchise_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `name` VARCHAR(255) NOT NULL,
    `display_name` VARCHAR(150) NOT NULL,
    `code` VARCHAR(20) NOT NULL,
    `settings_package` JSON NULL DEFAULT NULL,
    `settings_use_global` TINYINT(1) NOT NULL DEFAULT 0,
    `next_id` INT NOT NULL DEFAULT 1,
    `registered_name` VARCHAR(255) NULL DEFAULT NULL,
    `registered_number` VARCHAR(255) NULL DEFAULT NULL,
    `registration_date` DATE NULL DEFAULT NULL,
    `vat_number` VARCHAR(50) NULL DEFAULT NULL,
    `can_tax` TINYINT(1) NOT NULL DEFAULT 0,
    `head_office_franchise` TINYINT(1) NOT NULL DEFAULT 0,
    `bank_information` JSON NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`franchise_id`),
    UNIQUE KEY `franchise_name` (`name`),
    UNIQUE KEY `franchise_display_name` (`display_name`),
    UNIQUE KEY `franchise_code` (`code`),
    INDEX (`head_office_franchise`),
    CONSTRAINT `franchises_ibfk_1` FOREIGN KEY (`province_id`)
        REFERENCES `lc_provinces` (`province_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `franchises_head_franchise_fk` FOREIGN KEY (`head_franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Franchise towns (lc_franchise_towns) - pivot
CREATE TABLE `lc_franchise_towns` (
    `franchise_town_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `town_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`franchise_town_id`),
    CONSTRAINT `franchise_towns_franchise_id_fk` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `franchise_town_ibfk_1` FOREIGN KEY (`town_id`)
        REFERENCES `lc_towns` (`town_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Franchise users (lc_franchise_users) - pivot
CREATE TABLE `lc_franchise_users` (
    `franchise_user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `franchise_user_role` VARCHAR(255) NOT NULL,
    `is_primary_of_franchise` TINYINT(1) NOT NULL DEFAULT 0,
    `is_default_franchise` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`franchise_user_id`),
    UNIQUE KEY `franchise_user_unique` (`franchise_id`, `user_id`),
    INDEX (`franchise_user_role`),
    CONSTRAINT `franchise_users_ibfk_1` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `franchise_users_ibfk_2` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- BUSINESS MODULE (bs_)
-- =============================================================================

-- Businesses (bs_businesses)
CREATE TABLE `bs_businesses` (
    `business_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `business_address_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `business_province_id` BIGINT UNSIGNED NOT NULL,
    `business_town_id` BIGINT UNSIGNED NOT NULL,
    `postal_address_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `business_reg_number` VARCHAR(255) NULL DEFAULT NULL,
    `business_reg_name` VARCHAR(255) NULL DEFAULT NULL,
    `business_trading_name` VARCHAR(255) NOT NULL,
    `business_legal_type` VARCHAR(255) NULL DEFAULT NULL,
    `business_trade_type` VARCHAR(255) NULL DEFAULT NULL,
    `business_VAT_number` VARCHAR(255) NULL DEFAULT NULL,
    `business_reg_date` DATE NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`business_id`),
    UNIQUE KEY `business_reg_number` (`business_reg_number`),
    INDEX `business_legal_type` (`business_legal_type`),
    INDEX `business_trade_type` (`business_trade_type`),
    CONSTRAINT `businesses_ibfk_1` FOREIGN KEY (`business_address_id`)
        REFERENCES `lc_addresses` (`address_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `businesses_ibfk_2` FOREIGN KEY (`postal_address_id`)
        REFERENCES `lc_addresses` (`address_id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `businesses_ibfk_3` FOREIGN KEY (`business_province_id`)
        REFERENCES `lc_provinces` (`province_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `businesses_ibfk_4` FOREIGN KEY (`business_town_id`)
        REFERENCES `lc_towns` (`town_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Business clients (bs_business_clients) - pivot
CREATE TABLE `bs_business_clients` (
    `business_client_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `business_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `client_role` VARCHAR(255) NOT NULL,
    `is_primary` TINYINT(1) NOT NULL,
    PRIMARY KEY (`business_client_id`),
    UNIQUE KEY (`business_id`, `user_id`),
    CONSTRAINT `business_clients_business_id_fk` FOREIGN KEY (`business_id`)
        REFERENCES `bs_businesses` (`business_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `business_clients_ibfk_2` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Business ownerships (bs_business_ownerships)
CREATE TABLE `bs_business_ownerships` (
    `business_ownership_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `business_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `consented_by_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `status` VARCHAR(255) NOT NULL,
    `consented_at` TIMESTAMP NULL DEFAULT NULL,
    `consent_method` VARCHAR(255) NULL DEFAULT NULL,
    `jurisdiction_acknowledged` TINYINT(1) NOT NULL DEFAULT 0,
    `jurisdiction_acknowledged_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`business_ownership_id`),
    CONSTRAINT `business_ownerships_business_id_fk` FOREIGN KEY (`business_id`)
        REFERENCES `bs_businesses` (`business_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `business_ownerships_ibfk_1` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `business_ownerships_ibfk_2` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `business_ownerships_ibfk_3` FOREIGN KEY (`consented_by_user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Customers (bs_customers) - polymorphic
CREATE TABLE `bs_customers` (
    `customer_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customerable_type` VARCHAR(255) NOT NULL,
    `customerable_id` BIGINT UNSIGNED NOT NULL,
    `credit_balance` BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`customer_id`),
    INDEX (`customerable_type`, `customerable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inquiries (bs_inquiries)
CREATE TABLE `bs_inquiries` (
    `inquiry_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `created_by_user_id` BIGINT UNSIGNED NOT NULL,
    `assigned_franchise_id` BIGINT UNSIGNED NOT NULL,
    `assigned_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `business_id` BIGINT UNSIGNED NOT NULL,
    `service_id` BIGINT UNSIGNED NOT NULL,
    `status` VARCHAR(30) NOT NULL,
    `message` TEXT NULL DEFAULT NULL,
    `assigned_at` TIMESTAMP NULL DEFAULT NULL,
    `first_viewed_at` TIMESTAMP NULL DEFAULT NULL,
    `closed_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`inquiry_id`),
    INDEX (`status`),
    INDEX (`assigned_franchise_id`, `status`),
    INDEX (`service_id`, `status`),
    CONSTRAINT `inquiries_ibfk_1` FOREIGN KEY (`created_by_user_id`)
        REFERENCES `usr_users` (`user_id`) ON DELETE CASCADE,
    CONSTRAINT `inquiries_ibfk_2` FOREIGN KEY (`business_id`)
        REFERENCES `bs_businesses` (`business_id`) ON DELETE CASCADE,
    CONSTRAINT `inquiries_ibfk_3` FOREIGN KEY (`service_id`)
        REFERENCES `it_services` (`service_id`) ON DELETE CASCADE,
    CONSTRAINT `inquiries_ibfk_6` FOREIGN KEY (`assigned_franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON DELETE CASCADE,
    CONSTRAINT `inquiries_ibfk_7` FOREIGN KEY (`assigned_user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inquiry messages (bs_inquiry_messages)
CREATE TABLE `bs_inquiry_messages` (
    `inquiry_message_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `inquiry_id` BIGINT UNSIGNED NOT NULL,
    `sender_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `message` TEXT NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`inquiry_message_id`),
    INDEX (`inquiry_id`, `created_at`),
    CONSTRAINT `inquiry_messages_ibfk_1` FOREIGN KEY (`inquiry_id`)
        REFERENCES `bs_inquiries` (`inquiry_id`) ON DELETE CASCADE,
    CONSTRAINT `inquiry_messages_ibfk_2` FOREIGN KEY (`sender_user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mandates (bs_mandates)
CREATE TABLE `bs_mandates` (
    `mandate_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `document_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `url_key` VARCHAR(30) NOT NULL,
    `mandate_type` VARCHAR(50) NOT NULL,
    `status` VARCHAR(30) NOT NULL,
    `province_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `town_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `remuneration_excl_vat` BIGINT UNSIGNED NULL DEFAULT NULL,
    `vat_amount` BIGINT UNSIGNED NULL DEFAULT NULL,
    `tax_rate` SMALLINT UNSIGNED NULL DEFAULT NULL,
    `signed_at_place` VARCHAR(255) NULL DEFAULT NULL,
    `signed_on` DATE NULL DEFAULT NULL,
    `last_saved_at` TIMESTAMP NULL DEFAULT NULL,
    `submitted_at` TIMESTAMP NULL DEFAULT NULL,
    `approved_at` TIMESTAMP NULL DEFAULT NULL,
    `rejected_at` TIMESTAMP NULL DEFAULT NULL,
    `rejection_reason` TEXT NULL DEFAULT NULL,
    `draft_payload` JSON NULL DEFAULT NULL,
    `snapshot_payload` JSON NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`mandate_id`),
    UNIQUE KEY (`url_key`),
    INDEX (`customer_id`),
    INDEX (`document_id`),
    INDEX (`url_key`),
    INDEX (`mandate_type`),
    INDEX (`status`),
    INDEX (`province_id`),
    INDEX (`town_id`),
    INDEX (`last_saved_at`),
    INDEX (`submitted_at`),
    INDEX (`approved_at`),
    INDEX (`rejected_at`),
    CONSTRAINT `mandates_ibfk_1` FOREIGN KEY (`customer_id`)
        REFERENCES `bs_customers` (`customer_id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `mandates_ibfk_2` FOREIGN KEY (`document_id`)
        REFERENCES `bk_documents` (`document_id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `mandates_ibfk_3` FOREIGN KEY (`province_id`)
        REFERENCES `lc_provinces` (`province_id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `mandates_ibfk_4` FOREIGN KEY (`town_id`)
        REFERENCES `lc_towns` (`town_id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- ITEM MODULE (it_)
-- =============================================================================

-- Services (it_services)
CREATE TABLE `it_services` (
    `service_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `service_name` VARCHAR(255) NOT NULL,
    `service_description` VARCHAR(255) NULL DEFAULT NULL,
    `order` TINYINT NOT NULL,
    `province_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`service_id`),
    UNIQUE KEY `service_name` (`service_name`),
    CONSTRAINT `services_province_id_fk` FOREIGN KEY (`province_id`)
        REFERENCES `lc_provinces` (`province_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Items (it_items)
CREATE TABLE `it_items` (
    `item_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `service_id` BIGINT UNSIGNED NOT NULL,
    `province_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `type` VARCHAR(255) NOT NULL,
    `meta` JSON NULL DEFAULT NULL,
    PRIMARY KEY (`item_id`),
    UNIQUE KEY (`slug`),
    CONSTRAINT `items_service_id_fk` FOREIGN KEY (`service_id`)
        REFERENCES `it_services` (`service_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `items_province_id_fk` FOREIGN KEY (`province_id`)
        REFERENCES `lc_provinces` (`province_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Item lines (it_item_lines)
CREATE TABLE `it_item_lines` (
    `item_line_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `item_id` BIGINT UNSIGNED NOT NULL,
    `account_id` BIGINT UNSIGNED NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `group` VARCHAR(255) NOT NULL,
    `price` BIGINT UNSIGNED NULL DEFAULT NULL,
    `included` TINYINT(1) NOT NULL DEFAULT 0,
    `deferrable` TINYINT(1) NOT NULL DEFAULT 0,
    `editable` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`item_line_id`),
    UNIQUE KEY (`slug`),
    CONSTRAINT `item_lines_item_id_fk` FOREIGN KEY (`item_id`)
        REFERENCES `it_items` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `item_lines_account_id_fk` FOREIGN KEY (`account_id`)
        REFERENCES `bk_accounts` (`account_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Business licenses (it_business_licenses)
CREATE TABLE `it_business_licenses` (
    `business_license_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `business_id` BIGINT UNSIGNED NOT NULL,
    `item_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `document_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `license_name` VARCHAR(255) NOT NULL,
    `license_description` VARCHAR(255) NOT NULL,
    `license_number` VARCHAR(255) NULL DEFAULT NULL,
    `license_status` VARCHAR(255) NOT NULL,
    `issue_date` DATE NULL DEFAULT NULL,
    `expiry_date` DATE NULL DEFAULT NULL,
    `renew_date` DATE NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`business_license_id`),
    UNIQUE KEY (`license_number`),
    INDEX (`license_status`),
    INDEX (`license_number`),
    CONSTRAINT `business_licenses_business_id_fk` FOREIGN KEY (`business_id`)
        REFERENCES `bs_businesses` (`business_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `business_licenses_document_id_fk` FOREIGN KEY (`document_id`)
        REFERENCES `bk_documents` (`document_id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- BOOKKEEPING MODULE (bk_)
-- =============================================================================

-- Accounts (bk_accounts)
CREATE TABLE `bk_accounts` (
    `account_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `account_type` VARCHAR(30) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `account_role` VARCHAR(50) NULL DEFAULT NULL,
    `in_direction` VARCHAR(50) NOT NULL,
    `out_direction` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`account_id`),
    UNIQUE KEY `uq_accounts_scope_code` (`franchise_id`, `code`),
    UNIQUE KEY `uq_accounts_scope_role` (`franchise_id`, `account_role`),
    INDEX `idx_accounts_type` (`account_type`),
    CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Financial categories (bk_financial_categories)
CREATE TABLE `bk_financial_categories` (
    `financial_category_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `parent_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `account_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `type` VARCHAR(20) NOT NULL,
    `financial_statement_section` VARCHAR(30) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `definition` TEXT NULL DEFAULT NULL,
    `examples` JSON NULL DEFAULT NULL,
    `boundary_rule` TEXT NULL DEFAULT NULL,
    `user_guidance` TEXT NULL DEFAULT NULL,
    `keyword_warnings` JSON NULL DEFAULT NULL,
    `keyword_warning_message` TEXT NULL DEFAULT NULL,
    `is_tax_deductible` TINYINT(1) NOT NULL DEFAULT 1,
    `requires_receipt` TINYINT(1) NOT NULL DEFAULT 1,
    `requires_counterparty` TINYINT(1) NOT NULL DEFAULT 0,
    `requires_beneficiary_details` TINYINT(1) NOT NULL DEFAULT 0,
    `requires_section_18a` TINYINT(1) NOT NULL DEFAULT 0,
    `requires_vehicle_reference` TINYINT(1) NOT NULL DEFAULT 0,
    `requires_travel_details` TINYINT(1) NOT NULL DEFAULT 0,
    `is_system_controlled` TINYINT(1) NOT NULL DEFAULT 0,
    `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`financial_category_id`),
    UNIQUE KEY (`slug`),
    INDEX `idx_financial_categories_type` (`type`),
    INDEX `idx_financial_categories_fss` (`financial_statement_section`),
    INDEX `idx_financial_categories_system_controlled` (`is_system_controlled`),
    CONSTRAINT `financial_categories_parent_id_fk` FOREIGN KEY (`parent_id`)
        REFERENCES `bk_financial_categories` (`financial_category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_financial_categories_account_id` FOREIGN KEY (`account_id`)
        REFERENCES `bk_accounts` (`account_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Documents (bk_documents)
CREATE TABLE `bk_documents` (
    `document_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `parent_document_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `customer_id` BIGINT UNSIGNED NOT NULL,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `document_type` VARCHAR(32) NOT NULL,
    `number` VARCHAR(90) NOT NULL,
    `number_ui_string` TINYTEXT NOT NULL,
    `status` VARCHAR(255) NOT NULL,
    `terms` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NULL DEFAULT NULL,
    `url_key` VARCHAR(30) NULL DEFAULT NULL,
    `is_read_only` TINYINT(1) NOT NULL DEFAULT 0,
    `is_altered` TINYINT(1) NOT NULL DEFAULT 0,
    `bill_to_name` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_reg_number` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_trading_as` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_vat_number` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_street` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_town` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_postal_code` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_contact_full_name` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_contact_primary_cell` VARCHAR(255) NULL DEFAULT NULL,
    `bill_to_contact_primary_email` VARCHAR(255) NULL DEFAULT NULL,
    `user_full_name` VARCHAR(255) NULL DEFAULT NULL,
    `user_primary_cell` VARCHAR(255) NULL DEFAULT NULL,
    `user_primary_email` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_display_name` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_registered_name` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_registered_number` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_vat_number` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_can_tax` TINYINT NOT NULL DEFAULT 0,
    `issuer_bank_account_holder` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_bank_name` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_bank_account_number` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_bank_branch_code` VARCHAR(255) NULL DEFAULT NULL,
    `issuer_bank_account_type` VARCHAR(255) NULL DEFAULT NULL,
    `currency_code` VARCHAR(255) NOT NULL DEFAULT 'ZAR',
    `currency_symbol` VARCHAR(255) NOT NULL DEFAULT 'R',
    `tax_rate` TINYINT NULL DEFAULT NULL,
    `discount_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `subtotal` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `tax_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `application_fee_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `grand_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `royalty_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `profit_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `paid` BIGINT UNSIGNED NULL DEFAULT NULL,
    `balance` BIGINT UNSIGNED NULL DEFAULT NULL,
    `date` DATE NULL DEFAULT NULL,
    `date_ui_string` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`document_id`),
    UNIQUE KEY (`number`),
    UNIQUE KEY (`url_key`),
    INDEX (`document_type`, `status`),
    INDEX (`user_id`),
    INDEX (`parent_document_id`),
    INDEX (`date`),
    CONSTRAINT `documents_parent_document_id_fk` FOREIGN KEY (`parent_document_id`)
        REFERENCES `bk_documents` (`document_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`customer_id`)
        REFERENCES `bs_customers` (`customer_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `documents_ibfk_3` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Document lines (bk_document_lines)
CREATE TABLE `bk_document_lines` (
    `document_line_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `document_id` BIGINT UNSIGNED NOT NULL,
    `parent_line_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `account_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `service_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NULL DEFAULT NULL,
    `group` VARCHAR(255) NOT NULL,
    `deferrable` TINYINT(1) NOT NULL DEFAULT 0,
    `included` TINYINT(1) NOT NULL DEFAULT 1,
    `editable` TINYINT(1) NOT NULL DEFAULT 0,
    `price` BIGINT UNSIGNED NULL DEFAULT NULL,
    `paid` BIGINT UNSIGNED NULL DEFAULT NULL,
    `is_settled` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`document_line_id`),
    INDEX (`parent_line_id`),
    INDEX (`service_id`),
    INDEX (`slug`),
    CONSTRAINT `document_lines_parent_line_id_fk` FOREIGN KEY (`parent_line_id`)
        REFERENCES `bk_document_lines` (`document_line_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `document_lines_document_id_fk` FOREIGN KEY (`document_id`)
        REFERENCES `bk_documents` (`document_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `document_lines_service_id_fk` FOREIGN KEY (`service_id`)
        REFERENCES `it_services` (`service_id`) ON DELETE RESTRICT,
    CONSTRAINT `document_lines_account_id_fk` FOREIGN KEY (`account_id`)
        REFERENCES `bk_accounts` (`account_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Document line profits (bk_document_line_profits)
CREATE TABLE `bk_document_line_profits` (
    `document_line_profit_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `document_line_id` BIGINT UNSIGNED NOT NULL,
    `pro_fee` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `misc_fee` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `discount` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `tax_rate` TINYINT NULL DEFAULT NULL,
    `tax_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `royalty_percentage` TINYINT NOT NULL,
    `royalty_amount` BIGINT UNSIGNED NOT NULL,
    `total_profit` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`document_line_profit_id`),
    CONSTRAINT `document_line_profits_line_id_fk` FOREIGN KEY (`document_line_id`)
        REFERENCES `bk_document_lines` (`document_line_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expenses (bk_expenses)
CREATE TABLE `bk_expenses` (
    `expense_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `financial_creditor_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `description` VARCHAR(255) NULL DEFAULT NULL,
    `date` DATE NOT NULL,
    `amount_gross` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `amount_net` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `vat_amount` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `paid` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `balance` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `receipt_path` VARCHAR(255) NULL DEFAULT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'open',
    `type` VARCHAR(20) NOT NULL,
    `counterparty_name` VARCHAR(255) NULL DEFAULT NULL,
    `counterparty_purpose` VARCHAR(255) NULL DEFAULT NULL,
    `beneficiary_type` VARCHAR(30) NULL DEFAULT NULL,
    `section_18a_obtained` TINYINT(1) NOT NULL DEFAULT 0,
    `section_18a_certificate_path` VARCHAR(255) NULL DEFAULT NULL,
    `vehicle_reference` VARCHAR(255) NULL DEFAULT NULL,
    `travel_details` VARCHAR(255) NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`expense_id`),
    INDEX `idx_expenses_date` (`date`),
    INDEX `idx_expenses_status` (`status`),
    INDEX `idx_expenses_type` (`type`),
    CONSTRAINT `fk_expenses_franchise_id` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_expenses_user_id` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expense lines (bk_expense_lines)
CREATE TABLE `bk_expense_lines` (
    `expense_line_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `expense_id` BIGINT UNSIGNED NOT NULL,
    `financial_category_id` BIGINT UNSIGNED NOT NULL,
    `description` VARCHAR(255) NULL DEFAULT NULL,
    `amount_gross` BIGINT UNSIGNED NOT NULL,
    `amount_net` BIGINT UNSIGNED NOT NULL,
    `vat_amount` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `vat_rate` TINYINT UNSIGNED NULL DEFAULT NULL,
    `is_vat_claimable` TINYINT(1) NOT NULL DEFAULT 1,
    `is_tax_deductible` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`expense_line_id`),
    CONSTRAINT `fk_expense_lines_expense_id` FOREIGN KEY (`expense_id`)
        REFERENCES `bk_expenses` (`expense_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_expense_lines_financial_category_id` FOREIGN KEY (`financial_category_id`)
        REFERENCES `bk_financial_categories` (`financial_category_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Journal entries (bk_journal_entries)
CREATE TABLE `bk_journal_entries` (
    `journal_entry_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ledger_franchise_id` BIGINT UNSIGNED NOT NULL,
    `entry_type` VARCHAR(50) NOT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'posted',
    `posted_at` DATETIME NOT NULL,
    `memo` VARCHAR(500) NULL DEFAULT NULL,
    `reference_type` VARCHAR(255) NULL DEFAULT NULL,
    `reference_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`journal_entry_id`),
    INDEX `idx_journal_entries_posted_at` (`posted_at`),
    CONSTRAINT `journal_entries_ibfk_1` FOREIGN KEY (`ledger_franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Journal lines (bk_journal_lines)
CREATE TABLE `bk_journal_lines` (
    `journal_line_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `journal_entry_id` BIGINT UNSIGNED NOT NULL,
    `account_id` BIGINT UNSIGNED NOT NULL,
    `direction` VARCHAR(6) NOT NULL,
    `amount` BIGINT UNSIGNED NOT NULL,
    `counterparty_type` VARCHAR(50) NULL DEFAULT NULL,
    `counterparty_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`journal_line_id`),
    CONSTRAINT `journal_lines_entry_id_fk` FOREIGN KEY (`journal_entry_id`)
        REFERENCES `bk_journal_entries` (`journal_entry_id`) ON DELETE CASCADE,
    CONSTRAINT `journal_lines_ibfk_1` FOREIGN KEY (`account_id`)
        REFERENCES `bk_accounts` (`account_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payments (bk_payments)
CREATE TABLE `bk_payments` (
    `payment_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `customer_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `from_account_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `to_account_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `type` VARCHAR(30) NOT NULL,
    `amount` BIGINT UNSIGNED NOT NULL,
    `allocated_amount` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `status` VARCHAR(20) NOT NULL,
    `transaction_at` DATETIME NOT NULL,
    `reference` VARCHAR(255) NULL DEFAULT NULL,
    `notes` TEXT NULL DEFAULT NULL,
    `source` VARCHAR(20) NOT NULL DEFAULT 'manual',
    `payment_import_row_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`payment_id`),
    INDEX `idx_payments_type` (`type`),
    INDEX `idx_payments_status` (`status`),
    INDEX `idx_payments_transaction_at` (`transaction_at`),
    INDEX `idx_payments_reference` (`reference`),
    CONSTRAINT `fk_payments_ledger_franchise_id` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_payments_customer_id` FOREIGN KEY (`customer_id`)
        REFERENCES `bs_customers` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_payments_from_account_id` FOREIGN KEY (`from_account_id`)
        REFERENCES `bk_accounts` (`account_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_payments_to_account_id` FOREIGN KEY (`to_account_id`)
        REFERENCES `bk_accounts` (`account_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment allocations (bk_payment_allocations)
CREATE TABLE `bk_payment_allocations` (
    `payment_allocation_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `payment_id` BIGINT UNSIGNED NOT NULL,
    `allocated_by_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `confirmed_by_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `reversed_by_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `account_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `payable_type` VARCHAR(255) NOT NULL,
    `payable_id` BIGINT UNSIGNED NOT NULL,
    `amount_applied` BIGINT UNSIGNED NOT NULL,
    `status` VARCHAR(20) NOT NULL,
    `confirmed_at` DATETIME NULL DEFAULT NULL,
    `reversed_at` DATETIME NULL DEFAULT NULL,
    `reversal_reason` TEXT NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`payment_allocation_id`),
    INDEX (`payable_type`, `payable_id`),
    INDEX `idx_payment_allocations_status` (`status`),
    CONSTRAINT `fk_payment_allocations_payment_id` FOREIGN KEY (`payment_id`)
        REFERENCES `bk_payments` (`payment_id`) ON DELETE CASCADE,
    CONSTRAINT `fk_pa_allocated_by_user_id` FOREIGN KEY (`allocated_by_user_id`)
        REFERENCES `usr_users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_pa_confirmed_by_user_id` FOREIGN KEY (`confirmed_by_user_id`)
        REFERENCES `usr_users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_pa_reversed_by_user_id` FOREIGN KEY (`reversed_by_user_id`)
        REFERENCES `usr_users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- FINANCE MODULE (fn_)
-- =============================================================================

-- Franchise metrics (fn_franchise_metrics)
CREATE TABLE `fn_franchise_metrics` (
    `franchise_metric_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `year` SMALLINT UNSIGNED NOT NULL,
    `month` TINYINT UNSIGNED NOT NULL,
    `quoted_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `invoiced_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `paid_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `expected_profit` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `actual_profit` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `target_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`franchise_metric_id`),
    UNIQUE KEY `franchise_metrics_unique` (`franchise_id`, `year`, `month`),
    INDEX (`year`, `month`),
    CONSTRAINT `franchise_metrics_ibfk_1` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- LICENSE MODULE (ls_)
-- =============================================================================

-- License applications (ls_license_applications)
CREATE TABLE `ls_license_applications` (
    `license_application_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `province_id` BIGINT UNSIGNED NOT NULL,
    `customer_id` BIGINT UNSIGNED NOT NULL,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `license_application_code` VARCHAR(255) NOT NULL,
    `reference` VARCHAR(255) NULL DEFAULT NULL,
    `license_type` VARCHAR(255) NOT NULL,
    `item_slug` VARCHAR(255) NOT NULL,
    `legal_section` VARCHAR(255) NULL DEFAULT NULL,
    `legal_type` VARCHAR(255) NULL DEFAULT NULL,
    `status` VARCHAR(255) NOT NULL,
    `payload` JSON NULL DEFAULT NULL,
    `meta` JSON NULL DEFAULT NULL,
    `expected_profit` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `start_on_date` DATE NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`license_application_id`),
    UNIQUE KEY (`license_application_code`),
    UNIQUE KEY (`reference`),
    INDEX (`province_id`),
    INDEX (`item_slug`),
    INDEX (`status`),
    INDEX `la_license_type_legal_section_index` (`license_type`, `legal_section`),
    CONSTRAINT `license_applications_ibfk_1` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `license_applications_ibfk_2` FOREIGN KEY (`province_id`)
        REFERENCES `lc_provinces` (`province_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `license_applications_ibfk_3` FOREIGN KEY (`customer_id`)
        REFERENCES `bs_customers` (`customer_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `license_applications_ibfk_4` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- License application documents (ls_license_application_documents) - pivot
CREATE TABLE `ls_license_application_documents` (
    `license_application_document_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_application_id` BIGINT UNSIGNED NOT NULL,
    `document_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`license_application_document_id`),
    UNIQUE KEY `lad_license_application_document_unique` (`license_application_id`, `document_id`),
    INDEX (`license_application_id`),
    INDEX (`document_id`),
    CONSTRAINT `license_application_documents_ibfk_1` FOREIGN KEY (`license_application_id`)
        REFERENCES `ls_license_applications` (`license_application_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `license_application_documents_ibfk_2` FOREIGN KEY (`document_id`)
        REFERENCES `bk_documents` (`document_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- License application steps (ls_license_application_steps)
CREATE TABLE `ls_license_application_steps` (
    `license_application_step_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_application_id` BIGINT UNSIGNED NOT NULL,
    `key` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `position` INT UNSIGNED NOT NULL,
    `completion_required` TINYINT(1) NOT NULL DEFAULT 1,
    `status` VARCHAR(255) NOT NULL,
    `payload` JSON NULL DEFAULT NULL,
    `due_at` TIMESTAMP NULL DEFAULT NULL,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`license_application_step_id`),
    INDEX `las_status_index` (`status`),
    INDEX `las_license_application_id_position_index` (`license_application_id`, `position`),
    CONSTRAINT `las_fk_la` FOREIGN KEY (`license_application_id`)
        REFERENCES `ls_license_applications` (`license_application_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- License application step documents (ls_license_application_step_documents)
CREATE TABLE `ls_license_application_step_documents` (
    `license_application_step_document_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_application_step_id` BIGINT UNSIGNED NOT NULL,
    `uploader_type` VARCHAR(255) NOT NULL,
    `uploader_id` BIGINT UNSIGNED NOT NULL,
    `type` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `disk` VARCHAR(255) NOT NULL DEFAULT 'public',
    `path` TEXT NOT NULL,
    `status` VARCHAR(255) NOT NULL DEFAULT 'received',
    `notes` TEXT NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`license_application_step_document_id`),
    INDEX `lasd_uploader_index` (`uploader_type`, `uploader_id`),
    CONSTRAINT `lasd_fk` FOREIGN KEY (`license_application_step_id`)
        REFERENCES `ls_license_application_steps` (`license_application_step_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- SUPPORT MODULE - Tasks (sp_)
-- =============================================================================

-- Tasks (sp_tasks)
CREATE TABLE `sp_tasks` (
    `task_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `franchise_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `subject_type` VARCHAR(255) NOT NULL,
    `subject_id` BIGINT UNSIGNED NOT NULL,
    `type` VARCHAR(255) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `status` VARCHAR(255) NOT NULL,
    `priority` VARCHAR(255) NOT NULL,
    `due_at` DATE NOT NULL,
    `completed_at` DATETIME NULL DEFAULT NULL,
    `closed_at` DATETIME NULL DEFAULT NULL,
    `closed_reason` VARCHAR(255) NULL DEFAULT NULL,
    `snoozed_until` DATETIME NULL DEFAULT NULL,
    `meta` TEXT NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`task_id`),
    INDEX (`subject_type`, `subject_id`),
    INDEX `idx_assigned_status_due` (`user_id`, `status`, `due_at`),
    CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`franchise_id`)
        REFERENCES `lc_franchises` (`franchise_id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- MAIL MODULE (ml_)
-- =============================================================================

-- Mail templates (ml_mail_templates)
CREATE TABLE `ml_mail_templates` (
    `mail_template_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `province_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NULL DEFAULT NULL,
    `html` LONGTEXT NOT NULL,
    PRIMARY KEY (`mail_template_id`),
    UNIQUE KEY (`slug`),
    CONSTRAINT `mail_templates_province_id_fk` FOREIGN KEY (`province_id`)
        REFERENCES `lc_provinces` (`province_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Deliveries (ml_deliveries)
CREATE TABLE `ml_deliveries` (
    `delivery_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `deliverable_type` VARCHAR(255) NOT NULL,
    `deliverable_id` BIGINT UNSIGNED NOT NULL,
    `channel` VARCHAR(32) NOT NULL,
    `status` VARCHAR(32) NOT NULL,
    `to` VARCHAR(255) NULL DEFAULT NULL,
    `meta` JSON NULL DEFAULT NULL,
    `sent_at` TIMESTAMP NULL DEFAULT NULL,
    `failed_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`delivery_id`),
    INDEX (`deliverable_type`, `deliverable_id`, `channel`),
    CONSTRAINT `deliveries_user_id_fk` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User mail signatures (ml_user_mail_signatures)
CREATE TABLE `ml_user_mail_signatures` (
    `user_mail_signature_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `image_blob` LONGBLOB NOT NULL,
    `mime_type` VARCHAR(32) NOT NULL,
    `image_hash` VARCHAR(64) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`user_mail_signature_id`),
    CONSTRAINT `user_mail_signatures_user_id_fk` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mail accounts (ml_mail_accounts)
CREATE TABLE `ml_mail_accounts` (
    `mail_account_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `mail_template_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `account_name` VARCHAR(255) NULL DEFAULT NULL,
    `imap_host` VARCHAR(255) NULL DEFAULT NULL,
    `imap_port` SMALLINT NULL DEFAULT NULL,
    `imap_encryption` VARCHAR(255) NULL DEFAULT NULL,
    `imap_validate_cert` TINYINT(1) NOT NULL DEFAULT 1,
    `smtp_host` VARCHAR(255) NULL DEFAULT NULL,
    `smtp_port` SMALLINT NULL DEFAULT NULL,
    `smtp_encryption` VARCHAR(255) NULL DEFAULT NULL,
    `smtp_username` VARCHAR(255) NULL DEFAULT NULL,
    `smtp_password` TEXT NULL DEFAULT NULL,
    `username` VARCHAR(255) NULL DEFAULT NULL,
    `password` TEXT NULL DEFAULT NULL,
    `enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`mail_account_id`),
    CONSTRAINT `mail_accounts_user_id_fk` FOREIGN KEY (`user_id`)
        REFERENCES `usr_users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `mail_accounts_template_id_fk` FOREIGN KEY (`mail_template_id`)
        REFERENCES `ml_mail_templates` (`mail_template_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
