-- Database migration for Site Settings and Mowaq Documents Module
-- Date: 2026-02-26

-- 1. Settings Table (Key-Value)
CREATE TABLE IF NOT EXISTS `settings` (
    `setting_key` VARCHAR(100) PRIMARY KEY,
    `setting_value` TEXT,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tax Rates Table
CREATE TABLE IF NOT EXISTS `tax_rates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `percentage` DECIMAL(5, 2) NOT NULL,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Document Types
CREATE TABLE IF NOT EXISTS `document_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(191) NOT NULL,
    `icon` VARCHAR(50) DEFAULT 'ti ti-file',
    `color` VARCHAR(20) DEFAULT 'blue',
    `description` TEXT,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `deleted_at` TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Documents
CREATE TABLE IF NOT EXISTS `documents` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `type_id` INT NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `doc_number` VARCHAR(100),
    `unified_number` VARCHAR(100),
    `country` VARCHAR(100),
    `issue_date` DATE,
    `expiry_date` DATE,
    `external_link` TEXT,
    `content_text` TEXT,
    `notes` TEXT,
    `status` ENUM('valid', 'expired', 'archived', 'pending') DEFAULT 'valid',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (`type_id`) REFERENCES `document_types`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Document Entity Links (Flexible/Polymorphic Linking)
CREATE TABLE IF NOT EXISTS `document_links` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `document_id` BIGINT NOT NULL,
    `entity_type` ENUM('portfolio', 'branch', 'center', 'project', 'party') NOT NULL,
    `entity_id` BIGINT NOT NULL,
    FOREIGN KEY (`document_id`) REFERENCES `documents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed default settings
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES 
('footer_right', 'جميع الحقوق محفوظة © 2026'),
('footer_center', 'نظام مالي سحابي ذكي'),
('footer_left', '{hijri_date} | {gregorian_date}'),
('hijri_correction', '0'),
('default_tax_id', '1');

-- Seed initial tax rate
INSERT IGNORE INTO `tax_rates` (`id`, `name`, `percentage`, `status`) VALUES 
(1, 'ضريبة القيمة المضافة (VAT)', 15.00, 'active');

-- Seed initial document types
INSERT IGNORE INTO `document_types` (`name`, `icon`, `color`) VALUES 
('سجل تجاري', 'ti ti-file-text', 'blue'),
('رخصة بلدية', 'ti ti-building', 'green'),
('شهادة ضريبية', 'ti ti-receipt', 'orange'),
('هوية / إقامة', 'ti ti-id-badge', 'red'),
('جواز سفر', 'ti ti-passport', 'purple'),
('عقد إيجار', 'ti ti-home', 'cyan'),
('وكالة شرعية', 'ti ti-user-check', 'indigo'),
('تقرير فني', 'ti ti-report', 'teal');