-- =====================================================================
-- سامانه مدیریت بیمه تکمیلی درمان پرسنل
-- اسکیمای پایگاه داده - MySQL 5.7+ / MariaDB 10.2+
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- جدول کاربران (کارمندان و مدیر/مالی)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    personnel_code VARCHAR(50) DEFAULT NULL,
    role ENUM('admin','employee') NOT NULL DEFAULT 'employee',
    email VARCHAR(150) DEFAULT NULL,
    hire_date DATE DEFAULT NULL, -- تاریخ استخدام؛ برای محاسبه تناسب سقف سال اول استفاده می‌شود
    agreement_accepted_version INT UNSIGNED NOT NULL DEFAULT 0, -- آخرین نسخه قرارداد/تعهدنامه‌ای که کاربر پذیرفته
    agreement_accepted_at TIMESTAMP NULL DEFAULT NULL,
    gender ENUM('male','female') DEFAULT NULL,
    national_code VARCHAR(20) DEFAULT NULL,
    id_number VARCHAR(30) DEFAULT NULL, -- شماره شناسنامه
    mobile VARCHAR(20) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    has_medical_condition TINYINT(1) DEFAULT NULL,
    medical_history TEXT DEFAULT NULL,
    pre_employment_test_required TINYINT(1) NOT NULL DEFAULT 0,
    pre_employment_test_date DATE DEFAULT NULL, -- در صورت مشمول بودن، ملاک محاسبه سقف به‌جای تاریخ استخدام همین تاریخ است
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    ui_template VARCHAR(20) NOT NULL DEFAULT 'modern', -- قالب ظاهری انتخابی کاربر: modern, classic, minimal
    ui_color VARCHAR(20) NOT NULL DEFAULT 'teal', -- رنگ اصلی انتخابی کاربر: teal, green, blue, red, orange, purple
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- مدارک هویتی کارمند اصلی (مشابه افراد تبعی)
CREATE TABLE IF NOT EXISTS user_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    doc_type ENUM('national_card_front','national_card_back','birth_cert_p1','birth_cert_p2','birth_cert_p3','birth_cert_p4','other') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- افراد تبعی (همسر، فرزند، پدر، مادر)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dependents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    relation ENUM('spouse','father','mother','child') NOT NULL,
    national_code VARCHAR(20) DEFAULT NULL,
    gender ENUM('male','female') DEFAULT NULL,
    id_number VARCHAR(30) DEFAULT NULL,
    mobile VARCHAR(20) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    has_medical_condition TINYINT(1) DEFAULT NULL,
    medical_history TEXT DEFAULT NULL,
    status ENUM('pending','approved','needs_correction') NOT NULL DEFAULT 'pending',
    admin_note TEXT DEFAULT NULL,
    reviewed_at TIMESTAMP NULL DEFAULT NULL,
    reviewed_by INT UNSIGNED DEFAULT NULL,
    consent_accepted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- مدارک هویتی افراد تبعی (کارت ملی و شناسنامه - تمام صفحات)
CREATE TABLE IF NOT EXISTS dependent_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dependent_id INT UNSIGNED NOT NULL,
    doc_type ENUM('national_card_front','national_card_back','birth_cert_p1','birth_cert_p2','birth_cert_p3','birth_cert_p4','other') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dependent_id) REFERENCES dependents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- تنظیمات کلی سامانه (کلید-مقدار)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- ردیف‌های درمانی (هر ردیف یک سقف حداکثر سالانه مستقل دارد)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS insurance_rows (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT DEFAULT NULL,
    required_documents TEXT DEFAULT NULL, -- هر مدرک در یک خط
    max_amount DECIMAL(18,0) NOT NULL DEFAULT 0, -- سقف فردی سالانه (ریال)
    gender_restriction ENUM('none','female_only','male_only') NOT NULL DEFAULT 'none',
    min_months_after_hire INT DEFAULT NULL, -- در صورت پر بودن، پیش از گذشت این تعداد ماه از تاریخ استخدام، سقف صفر است (فرمول تناسب روزانه برای این ردیف اعمال نمی‌شود)
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- سقف‌های ترکیبی (سقف مشترک بین چند ردیف درمانی)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS combined_caps (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT DEFAULT NULL,
    max_amount DECIMAL(18,0) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- رابطه چند به چند بین سقف ترکیبی و ردیف‌های درمانی
CREATE TABLE IF NOT EXISTS combined_cap_rows (
    cap_id INT UNSIGNED NOT NULL,
    row_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (cap_id, row_id),
    FOREIGN KEY (cap_id) REFERENCES combined_caps(id) ON DELETE CASCADE,
    FOREIGN KEY (row_id) REFERENCES insurance_rows(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- درخواست‌های هزینه (claims)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS claims (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    row_id INT UNSIGNED NOT NULL,
    claim_year INT NOT NULL, -- سال شمسی ثبت درخواست (برای محاسبه سقف سالانه)
    amount_requested DECIMAL(18,0) NOT NULL,
    amount_approved DECIMAL(18,0) DEFAULT NULL,
    status ENUM('pending','approved','rejected','paid','needs_correction') NOT NULL DEFAULT 'pending',
    description TEXT DEFAULT NULL,
    admin_note TEXT DEFAULT NULL,
    cap_limited TINYINT(1) NOT NULL DEFAULT 0, -- آیا مبلغ به دلیل رسیدن به سقف محدود شده
    is_dependent TINYINT(1) NOT NULL DEFAULT 0,
    dependent_id INT UNSIGNED DEFAULT NULL,
    medical_center VARCHAR(255) DEFAULT NULL,
    service_date VARCHAR(20) DEFAULT NULL, -- تاریخ شمسی انجام خدمت، به‌صورت متن (مثال: 1404/05/10)
    receipt_number VARCHAR(100) DEFAULT NULL,
    franchise_percent DECIMAL(5,2) NOT NULL DEFAULT 0, -- درصد فرانشیز اعمال‌شده در زمان تایید
    franchise_amount DECIMAL(18,0) NOT NULL DEFAULT 0,
    payable_amount DECIMAL(18,0) DEFAULT NULL, -- مبلغ نهایی قابل پرداخت پس از کسر فرانشیز و سهم افراد تبعی
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP NULL DEFAULT NULL,
    reviewed_by INT UNSIGNED DEFAULT NULL,
    paid_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (row_id) REFERENCES insurance_rows(id),
    FOREIGN KEY (reviewed_by) REFERENCES users(id),
    FOREIGN KEY (dependent_id) REFERENCES dependents(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- مدارک پیوست هر درخواست
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS claim_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    claim_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    item_label VARCHAR(255) DEFAULT NULL, -- کدام مدرک لازمِ ردیف درمانی (خط متناظر required_documents)
    uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (claim_id) REFERENCES claims(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- لاگ فعالیت‌ها (اختیاری ولی برای سابقه مفید است)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    claim_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    details TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- داده‌های نمونه اولیه — ردیف‌های درمانی
-- این مقادیر صرفاً نمونه هستند و باید از پنل مدیریت با اعداد واقعی
-- شرکت شما جایگزین شوند.
-- =====================================================================
INSERT INTO insurance_rows (name, description, required_documents, max_amount, gender_restriction, min_months_after_hire, sort_order) VALUES
('دندانپزشکی', 'هزینه‌های درمان و ترمیم دندان', 'کپی صورتحساب دندانپزشکی\nنسخه/گزارش درمان\nکپی کارت ملی', 50000000, 'none', NULL, 1),
('عینک و لنز', 'خرید عینک طبی یا لنز تماسی', 'فاکتور خرید عینک/لنز\nنسخه چشم‌پزشک یا اپتومتریست', 20000000, 'none', NULL, 2),
('ویزیت و پاراکلینیک', 'ویزیت پزشک، آزمایش، رادیولوژی و سونوگرافی', 'فیش پرداخت ویزیت/آزمایش\nنسخه پزشک', 30000000, 'none', NULL, 3),
('دارو', 'هزینه خرید دارو با نسخه پزشک', 'نسخه پزشک\nفاکتور داروخانه', 15000000, 'none', NULL, 4),
('جراحی و بیمارستانی', 'هزینه‌های بستری، جراحی و بیمارستان', 'صورتحساب بیمارستان\nگزارش پزشکی/جراحی\nکپی دفترچه یا بیمه پایه', 200000000, 'none', NULL, 5),
('زایمان', 'هزینه‌های زایمان طبیعی یا سزارین', 'صورتحساب بیمارستان\nگزارش پزشکی زایمان', 100000000, 'female_only', 9, 6);

-- سقف‌های ترکیبی نمونه
INSERT INTO combined_caps (name, description, max_amount) VALUES
('سقف ترکیبی درمان سرپایی (پاراکلینیک و دارو)', 'مجموع ویزیت/پاراکلینیک و دارو نباید از این سقف بیشتر شود', 40000000),
('سقف کل سالانه بیمه تکمیلی', 'مجموع تمام ردیف‌های درمانی برای هر پرسنل در سال', 300000000);

-- اتصال ردیف‌ها به سقف‌های ترکیبی
-- سقف ۱: ویزیت و پاراکلینیک (id=3) + دارو (id=4)
INSERT INTO combined_cap_rows (cap_id, row_id) VALUES (1, 3), (1, 4);
-- سقف ۲: همه ردیف‌ها
INSERT INTO combined_cap_rows (cap_id, row_id) VALUES (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6);

-- تنظیمات پیش‌فرض سامانه (متن قرارداد و اطلاعات شرکت جداگانه توسط install.php ثبت می‌شود)
INSERT INTO settings (setting_key, setting_value) VALUES
('dependents_enabled', '1'),
('franchise_percent', '10'),
('dependent_share_percent', '50'),
('agreement_version', '1'),
('proration_first_year_only', '1');
