USE expenses;

CREATE TABLE IF NOT EXISTS user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    display_name VARCHAR(150) NOT NULL DEFAULT '',
    employee_id VARCHAR(50) NOT NULL DEFAULT '',
    mobile_number VARCHAR(20) NOT NULL DEFAULT '',
    department VARCHAR(100) NOT NULL DEFAULT '',
    date_of_joining DATE NULL DEFAULT NULL,
    date_of_birth DATE NULL DEFAULT NULL,
    designation VARCHAR(120) NOT NULL DEFAULT '',
    role_name VARCHAR(120) NOT NULL DEFAULT 'Employee',
    submits_to VARCHAR(150) NOT NULL DEFAULT '-',
    policy_name VARCHAR(150) NOT NULL DEFAULT 'default',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_profiles_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_user_profiles_org_user (organization_id, user_id),
    INDEX idx_user_profiles_org (organization_id)
);

CREATE TABLE IF NOT EXISTS user_bank_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    bank_name VARCHAR(120) NOT NULL DEFAULT '',
    account_holder_name VARCHAR(150) NOT NULL DEFAULT '',
    account_number VARCHAR(30) NOT NULL DEFAULT '',
    ifsc_code VARCHAR(20) NOT NULL DEFAULT '',
    branch_name VARCHAR(120) NOT NULL DEFAULT '',
    account_type ENUM('savings', 'current', 'salary', 'other') NOT NULL DEFAULT 'savings',
    is_primary TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_bank_accounts_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_bank_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_bank_accounts_org_user_primary (organization_id, user_id, is_primary)
);

CREATE TABLE IF NOT EXISTS user_delegates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    delegate_name VARCHAR(150) NOT NULL DEFAULT '',
    delegate_email VARCHAR(190) NOT NULL DEFAULT '',
    can_create TINYINT(1) NOT NULL DEFAULT 1,
    can_submit TINYINT(1) NOT NULL DEFAULT 1,
    can_approve TINYINT(1) NOT NULL DEFAULT 0,
    start_date DATE NULL DEFAULT NULL,
    end_date DATE NULL DEFAULT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('active', 'inactive', 'expired') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_delegates_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_delegates_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_delegates_org_user_status (organization_id, user_id, status)
);

CREATE TABLE IF NOT EXISTS user_out_of_office_approvers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    approver_name VARCHAR(150) NOT NULL DEFAULT '',
    approver_email VARCHAR(190) NOT NULL DEFAULT '',
    can_approve TINYINT(1) NOT NULL DEFAULT 1,
    start_date DATE NULL DEFAULT NULL,
    end_date DATE NULL DEFAULT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('active', 'inactive', 'expired') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_ooa_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_ooa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_ooa_org_user_status (organization_id, user_id, status)
);

CREATE TABLE IF NOT EXISTS user_receipt_forwarding (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    forward_email VARCHAR(190) NOT NULL DEFAULT '',
    provider_name VARCHAR(50) NOT NULL DEFAULT 'Gmail',
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    notes VARCHAR(255) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_receipt_forwarding_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_receipt_forwarding_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_user_receipt_forwarding_org_user (organization_id, user_id)
);

CREATE TABLE IF NOT EXISTS user_default_trip_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    trip_type VARCHAR(80) NOT NULL DEFAULT 'Business',
    travel_mode VARCHAR(80) NOT NULL DEFAULT 'Economy',
    billable_to VARCHAR(120) NOT NULL DEFAULT 'Personal',
    project_name VARCHAR(150) NOT NULL DEFAULT '',
    client_name VARCHAR(150) NOT NULL DEFAULT '',
    default_currency VARCHAR(12) NOT NULL DEFAULT 'INR',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_default_trip_values_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_default_trip_values_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_user_default_trip_values_org_user (organization_id, user_id)
);

CREATE TABLE IF NOT EXISTS user_default_expense_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    expense_category VARCHAR(120) NOT NULL DEFAULT '',
    expense_type VARCHAR(120) NOT NULL DEFAULT '',
    payment_mode VARCHAR(80) NOT NULL DEFAULT 'Cash',
    default_currency VARCHAR(12) NOT NULL DEFAULT 'INR',
    tax_included TINYINT(1) NOT NULL DEFAULT 0,
    comments VARCHAR(255) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_default_expense_values_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_default_expense_values_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_user_default_expense_values_org_user (organization_id, user_id)
);

CREATE TABLE IF NOT EXISTS user_travel_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    profile_photo_path VARCHAR(255) NOT NULL DEFAULT '',
    salutation VARCHAR(20) NOT NULL DEFAULT '',
    first_name VARCHAR(80) NOT NULL DEFAULT '',
    middle_name VARCHAR(80) NOT NULL DEFAULT '',
    last_name VARCHAR(80) NOT NULL DEFAULT '',
    passport_name VARCHAR(150) NOT NULL DEFAULT '',
    nationality VARCHAR(100) NOT NULL DEFAULT '',
    date_of_birth DATE NULL DEFAULT NULL,
    gender VARCHAR(20) NOT NULL DEFAULT '',
    personal_email VARCHAR(190) NOT NULL DEFAULT '',
    personal_mobile VARCHAR(25) NOT NULL DEFAULT '',
    redress_number VARCHAR(80) NOT NULL DEFAULT '',
    known_traveler_number VARCHAR(80) NOT NULL DEFAULT '',
    fax_number VARCHAR(80) NOT NULL DEFAULT '',
    preferred_departure_airport VARCHAR(120) NOT NULL DEFAULT '',
    home_address_line1 VARCHAR(150) NOT NULL DEFAULT '',
    home_address_line2 VARCHAR(150) NOT NULL DEFAULT '',
    home_country VARCHAR(100) NOT NULL DEFAULT '',
    home_state VARCHAR(100) NOT NULL DEFAULT '',
    home_city VARCHAR(100) NOT NULL DEFAULT '',
    home_pin_code VARCHAR(20) NOT NULL DEFAULT '',
    home_phone VARCHAR(25) NOT NULL DEFAULT '',
    work_address_line1 VARCHAR(150) NOT NULL DEFAULT '',
    work_address_line2 VARCHAR(150) NOT NULL DEFAULT '',
    work_country VARCHAR(100) NOT NULL DEFAULT '',
    work_state VARCHAR(100) NOT NULL DEFAULT '',
    work_city VARCHAR(100) NOT NULL DEFAULT '',
    work_pin_code VARCHAR(20) NOT NULL DEFAULT '',
    work_phone VARCHAR(25) NOT NULL DEFAULT '',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_travel_profiles_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_travel_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_user_travel_profiles_org_user (organization_id, user_id)
);

CREATE TABLE IF NOT EXISTS user_loyalty_programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    travel_profile_id INT NULL DEFAULT NULL,
    program_type VARCHAR(120) NOT NULL DEFAULT '',
    program_name VARCHAR(120) NOT NULL DEFAULT '',
    program_number VARCHAR(100) NOT NULL DEFAULT '',
    provider_name VARCHAR(120) NOT NULL DEFAULT '',
    membership_number VARCHAR(100) NOT NULL DEFAULT '',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_loyalty_programs_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_loyalty_programs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_loyalty_programs_profile FOREIGN KEY (travel_profile_id) REFERENCES user_travel_profiles(id) ON DELETE SET NULL,
    INDEX idx_user_loyalty_programs_org_user_status (organization_id, user_id, status)
);

CREATE TABLE IF NOT EXISTS user_travel_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    travel_profile_id INT NULL DEFAULT NULL,
    document_name VARCHAR(150) NOT NULL DEFAULT '',
    document_type ENUM('passport', 'visa', 'id_card', 'other') NOT NULL DEFAULT 'passport',
    document_country VARCHAR(100) NOT NULL DEFAULT '',
    document_number VARCHAR(120) NOT NULL DEFAULT '',
    issued_on DATE NULL DEFAULT NULL,
    valid_from DATE NULL DEFAULT NULL,
    expires_on DATE NULL DEFAULT NULL,
    file_path VARCHAR(255) NOT NULL DEFAULT '',
    consent_given TINYINT(1) NOT NULL DEFAULT 0,
    description VARCHAR(255) NOT NULL DEFAULT '',
    is_shared_with_team TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('active', 'archived') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_travel_documents_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_travel_documents_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_travel_documents_profile FOREIGN KEY (travel_profile_id) REFERENCES user_travel_profiles(id) ON DELETE SET NULL,
    INDEX idx_user_travel_documents_org_user_status (organization_id, user_id, status)
);

CREATE TABLE IF NOT EXISTS user_budget_periods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    budget_label VARCHAR(60) NOT NULL DEFAULT 'Apr 2026 - Mar 2027',
    period_start DATE NULL DEFAULT NULL,
    period_end DATE NULL DEFAULT NULL,
    budget_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    currency_code VARCHAR(12) NOT NULL DEFAULT 'INR',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_budget_periods_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_budget_periods_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_budget_periods_org_user_active (organization_id, user_id, is_active)
);

CREATE OR REPLACE VIEW vw_user_settings_summary AS
SELECT
    u.id AS user_id,
    COALESCE(p.organization_id, uo.id) AS organization_id,
    COALESCE(p.display_name, u.business_name, '') AS display_name,
    COALESCE(p.employee_id, '') AS employee_id,
    COALESCE(p.department, '') AS department,
    COALESCE(p.designation, '') AS designation,
    COALESCE(b.bank_name, '') AS bank_name,
    COALESCE(f.forward_email, '') AS forward_email,
    COALESCE(t.passport_name, '') AS passport_name,
    COALESCE(bp.budget_label, '') AS budget_label,
    COALESCE(d.delegate_count, 0) AS delegate_count,
    COALESCE(o.approver_count, 0) AS approver_count,
    COALESCE(ld.document_count, 0) AS document_count
FROM users u
LEFT JOIN organizations uo ON uo.user_id = u.id
LEFT JOIN user_profiles p ON p.user_id = u.id AND p.organization_id = uo.id
LEFT JOIN (
    SELECT user_id, bank_name
    FROM user_bank_accounts
    WHERE is_primary = 1
) b ON b.user_id = u.id
LEFT JOIN user_receipt_forwarding f ON f.user_id = u.id AND f.is_enabled = 1
LEFT JOIN user_travel_profiles t ON t.user_id = u.id AND t.status = 'active'
LEFT JOIN user_budget_periods bp ON bp.user_id = u.id AND bp.is_active = 1
LEFT JOIN (
    SELECT user_id, COUNT(*) AS delegate_count
    FROM user_delegates
    WHERE status = 'active'
    GROUP BY user_id
) d ON d.user_id = u.id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS approver_count
    FROM user_out_of_office_approvers
    WHERE status = 'active'
    GROUP BY user_id
) o ON o.user_id = u.id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS document_count
    FROM user_travel_documents
    WHERE status = 'active'
    GROUP BY user_id
) ld ON ld.user_id = u.id;
