CREATE TABLE IF NOT EXISTS organization_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    logo_path VARCHAR(255) DEFAULT NULL,
    date_format VARCHAR(30) DEFAULT 'dd/MM/yyyy',
    field_separator VARCHAR(5) DEFAULT '/',
    primary_contact_name VARCHAR(150) DEFAULT NULL,
    primary_contact_email VARCHAR(150) DEFAULT NULL,
    sender_email VARCHAR(150) DEFAULT 'message-service@mail.zohopayroll.in',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_settings (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS branding_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    appearance ENUM('dark', 'light') NOT NULL DEFAULT 'dark',
    accent_color VARCHAR(30) NOT NULL DEFAULT 'blue',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_branding_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS work_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    location_name VARCHAR(150) NOT NULL,
    address_line1 VARCHAR(200) DEFAULT NULL,
    address_line2 VARCHAR(200) DEFAULT NULL,
    state VARCHAR(100) DEFAULT NULL,
    city VARCHAR(100) DEFAULT NULL,
    pincode VARCHAR(20) DEFAULT NULL,
    is_filing_address TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_location_name (organization_id, location_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    org_id VARCHAR(50) NOT NULL,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(80) DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_department_name (org_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS designations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    name VARCHAR(120) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_designation (organization_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    plan_name VARCHAR(120) NOT NULL DEFAULT '14-DAYS FREE TRIAL',
    billing_cycle VARCHAR(30) NOT NULL DEFAULT 'monthly',
    monthly_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    employee_limit INT NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'trial',
    trial_ends_on DATE DEFAULT NULL,
    feature_summary TEXT DEFAULT NULL,
    premium_feature_summary TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_subscription (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    role_name VARCHAR(150) NOT NULL,
    description VARCHAR(500) DEFAULT NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    permissions_json LONGTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_role (organization_id, role_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    full_name VARCHAR(180) NOT NULL,
    email VARCHAR(190) NOT NULL,
    role_name VARCHAR(150) NOT NULL DEFAULT 'Admin',
    status VARCHAR(30) NOT NULL DEFAULT 'ACTIVE',
    avatar_path VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_user_email (organization_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pay_schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    pay_frequency VARCHAR(100) NOT NULL DEFAULT 'Every month',
    working_days_json TEXT DEFAULT NULL,
    pay_day_label VARCHAR(120) DEFAULT NULL,
    first_pay_period VARCHAR(60) DEFAULT NULL,
    first_pay_date DATE DEFAULT NULL,
    working_days_count INT NOT NULL DEFAULT 0,
    upcoming_payrolls_json LONGTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_pay_schedule (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS statutory_components (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    component_code VARCHAR(40) NOT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 0,
    config_json LONGTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_component (organization_id, component_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS direct_deposit_setups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    provider_code VARCHAR(50) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    customer_id VARCHAR(120) DEFAULT NULL,
    password_part_one VARCHAR(120) DEFAULT NULL,
    password_part_two VARCHAR(120) DEFAULT NULL,
    bank_account_label VARCHAR(180) DEFAULT NULL,
    bank_account_number VARCHAR(50) DEFAULT NULL,
    live_balance DECIMAL(12,2) DEFAULT NULL,
    registered_mobile VARCHAR(20) DEFAULT NULL,
    secret_pin VARCHAR(20) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    last_synced_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_provider_setup (organization_id, provider_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS zoho_app_integrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    app_code VARCHAR(50) NOT NULL,
    app_name VARCHAR(120) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'inactive',
    connection_name VARCHAR(180) DEFAULT NULL,
    connected_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_app_integration (organization_id, app_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS developer_connections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    service_type VARCHAR(30) NOT NULL DEFAULT 'default',
    service_code VARCHAR(80) NOT NULL,
    service_name VARCHAR(150) NOT NULL,
    connection_name VARCHAR(150) NOT NULL,
    scope_summary TEXT DEFAULT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_connection_name (organization_id, connection_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incoming_webhooks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT DEFAULT NULL,
    script_body LONGTEXT DEFAULT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS data_backups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    backup_type VARCHAR(30) NOT NULL,
    requested_by VARCHAR(190) DEFAULT NULL,
    recipient_email VARCHAR(190) DEFAULT NULL,
    file_type VARCHAR(20) NOT NULL DEFAULT 'ZIP',
    export_status VARCHAR(30) NOT NULL DEFAULT 'queued',
    download_link VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    attachment_path VARCHAR(255) DEFAULT NULL,
    contact_number VARCHAR(30) DEFAULT NULL,
    criticality VARCHAR(30) NOT NULL DEFAULT 'None',
    created_by VARCHAR(190) DEFAULT NULL,
    created_email VARCHAR(190) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
