-- Create Database
CREATE DATABASE IF NOT EXISTS bimeh_app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_persian_ci;
USE bimeh_app_db;

-- 1. Users (100 users will be stored here)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    national_id VARCHAR(10) NOT NULL UNIQUE, -- Used for login
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(15),
    city VARCHAR(50),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Insurance Companies & Plans
CREATE TABLE IF NOT EXISTS insurance_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(50) NOT NULL, -- e.g., Iran, Day, Asia
    plan_name VARCHAR(50) NOT NULL,    -- e.g., Gold, Diamond, Silver
    annual_limit BIGINT NOT NULL,      -- Overall annual limit
    description TEXT,
    franchise_base_percent INT DEFAULT 10, -- Base franchise (e.g., 10%)
    has_dental BOOLEAN DEFAULT TRUE,
    has_optical BOOLEAN DEFAULT TRUE
);

-- 3. User Subscriptions (Connecting User to Plan)
CREATE TABLE IF NOT EXISTS user_subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    start_date DATE,
    end_date DATE,
    policy_number VARCHAR(50),
    covered_members INT DEFAULT 1, -- Family members count
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES insurance_plans(id)
);

-- 4. Coverage Rules (Specific limits per category)
CREATE TABLE IF NOT EXISTS coverage_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_id INT NOT NULL,
    category VARCHAR(50), -- e.g., 'dental', 'surgery', 'optical'
    limit_per_visit BIGINT,
    annual_category_limit BIGINT,
    franchise_percent INT, -- Specific franchise for this category
    waiting_period_days INT DEFAULT 0,
    required_documents TEXT, -- JSON string of required docs
    FOREIGN KEY (plan_id) REFERENCES insurance_plans(id)
);

-- 5. Service Providers (Doctors, Hospitals)
CREATE TABLE IF NOT EXISTS providers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    type VARCHAR(50), -- Hospital, Clinic, Dentist, Pharmacy
    specialty VARCHAR(100),
    city VARCHAR(50),
    address TEXT,
    phone VARCHAR(20),
    is_contracted BOOLEAN DEFAULT TRUE, -- Tarafe Gharardad
    contract_companies TEXT -- JSON array of companies supported e.g. ["Iran", "Day"]
);

-- 6. Claims & History (Financials)
CREATE TABLE IF NOT EXISTS claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    provider_name VARCHAR(100),
    service_date DATE,
    category VARCHAR(50),
    total_cost BIGINT,
    insurance_share BIGINT,
    user_share BIGINT, -- Franchise paid
    status ENUM('pending', 'approved', 'rejected', 'paid') DEFAULT 'pending',
    rejection_reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);