-- Migration: Employee profile and history tables
-- Date: 2025-13-01 (synthetic future date for ordering)

-- Employee departments lookup
CREATE TABLE IF NOT EXISTS employee_departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS employee_departments_name_uidx
    ON employee_departments (LOWER(name))
    WHERE isDeleted = FALSE;

-- Employee designations lookup
CREATE TABLE IF NOT EXISTS employee_designations (
    id SERIAL PRIMARY KEY,
    department_id INTEGER DEFAULT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS employee_designations_department_idx
    ON employee_designations (department_id)
    WHERE isDeleted = FALSE;

CREATE UNIQUE INDEX IF NOT EXISTS employee_designations_name_uidx
    ON employee_designations (LOWER(name))
    WHERE isDeleted = FALSE;

-- Employee profile table (1:1 with users)
CREATE TABLE IF NOT EXISTS employee_profiles (
    id SERIAL PRIMARY KEY,
    user_id uuid NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    contact_number VARCHAR(50) DEFAULT NULL,
    alternate_contact VARCHAR(50) DEFAULT NULL,
    emergency_contact_name VARCHAR(150) DEFAULT NULL,
    emergency_contact_number VARCHAR(50) DEFAULT NULL,
    address_line1 TEXT DEFAULT NULL,
    address_line2 TEXT DEFAULT NULL,
    city VARCHAR(120) DEFAULT NULL,
    state VARCHAR(120) DEFAULT NULL,
    postal_code VARCHAR(30) DEFAULT NULL,
    country VARCHAR(120) DEFAULT NULL,
    id_proof_type VARCHAR(100) DEFAULT NULL,
    id_proof_number VARCHAR(120) DEFAULT NULL,
    reporting_manager_id uuid DEFAULT NULL,
    department_id INTEGER DEFAULT NULL,
    designation_id INTEGER DEFAULT NULL,
    joining_date DATE DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS employee_profiles_user_uidx
    ON employee_profiles (user_id)
    WHERE isDeleted = FALSE;

CREATE INDEX IF NOT EXISTS employee_profiles_department_idx
    ON employee_profiles (department_id)
    WHERE isDeleted = FALSE;

CREATE INDEX IF NOT EXISTS employee_profiles_designation_idx
    ON employee_profiles (designation_id)
    WHERE isDeleted = FALSE;

CREATE INDEX IF NOT EXISTS employee_profiles_reporting_manager_idx
    ON employee_profiles (reporting_manager_id)
    WHERE isDeleted = FALSE;

-- Employment history table (many per user)
CREATE TABLE IF NOT EXISTS employee_history (
    id SERIAL PRIMARY KEY,
    user_id uuid NOT NULL,
    employer VARCHAR(255) NOT NULL,
    department_id INTEGER DEFAULT NULL,
    designation_id INTEGER DEFAULT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    responsibilities TEXT DEFAULT NULL,
    document_id uuid DEFAULT NULL,
    is_current BOOLEAN NOT NULL DEFAULT FALSE,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS employee_history_user_idx
    ON employee_history (user_id)
    WHERE isDeleted = FALSE;

CREATE INDEX IF NOT EXISTS employee_history_department_idx
    ON employee_history (department_id)
    WHERE isDeleted = FALSE;

CREATE INDEX IF NOT EXISTS employee_history_designation_idx
    ON employee_history (designation_id)
    WHERE isDeleted = FALSE;

-- Seed baseline departments and designations
INSERT INTO employee_departments (id, name, description)
VALUES
    (1, 'Executive', 'Executive leadership and management'),
    (2, 'Human Resources', 'Human resource management'),
    (3, 'Finance', 'Accounting and finance operations'),
    (4, 'Operations', 'Core operational staff'),
    (5, 'IT Services', 'Technology and digital services')
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    isDeleted = FALSE,
    updatedAt = now();

INSERT INTO employee_designations (id, department_id, name, description)
VALUES
    (1, 1, 'Chief Executive Officer', 'Head of the organisation'),
    (2, 1, 'Director', 'Departmental director'),
    (3, 2, 'HR Manager', 'Human resources manager'),
    (4, 2, 'HR Officer', 'Human resources officer'),
    (5, 3, 'Finance Manager', 'Finance and accounting manager'),
    (6, 3, 'Accountant', 'Staff accountant'),
    (7, 4, 'Operations Manager', 'Operations leadership'),
    (8, 4, 'Operations Executive', 'Operations specialist'),
    (9, 5, 'IT Manager', 'Technology leadership'),
    (10, 5, 'Software Engineer', 'Software development role')
ON CONFLICT (id) DO UPDATE SET
    department_id = EXCLUDED.department_id,
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    isDeleted = FALSE,
    updatedAt = now();

-- Form definitions for profile and employment history
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
    214,
    'Employee Profile Details',
    'Capture employee personal and job profile information',
    'employee_profiles',
    $$[
        {"label":"Date of Birth","field_name":"date_of_birth","data_column":"date_of_birth","type_id":3,"order_index":1},
        {"label":"Contact Number","field_name":"contact_number","data_column":"contact_number","type_id":1,"order_index":2},
        {"label":"Alternate Contact","field_name":"alternate_contact","data_column":"alternate_contact","type_id":1,"order_index":3},
        {"label":"Emergency Contact Name","field_name":"emergency_contact_name","data_column":"emergency_contact_name","type_id":1,"order_index":4},
        {"label":"Emergency Contact Number","field_name":"emergency_contact_number","data_column":"emergency_contact_number","type_id":1,"order_index":5},
        {"label":"Address Line 1","field_name":"address_line1","data_column":"address_line1","type_id":1,"order_index":6},
        {"label":"Address Line 2","field_name":"address_line2","data_column":"address_line2","type_id":1,"order_index":7},
        {"label":"City","field_name":"city","data_column":"city","type_id":1,"order_index":8},
        {"label":"State/Province","field_name":"state","data_column":"state","type_id":1,"order_index":9},
        {"label":"Postal Code","field_name":"postal_code","data_column":"postal_code","type_id":1,"order_index":10},
        {"label":"Country","field_name":"country","data_column":"country","type_id":1,"order_index":11},
        {"label":"ID Proof Type","field_name":"id_proof_type","data_column":"id_proof_type","type_id":4,"order_index":12,
            "options":[
                {"value":"passport","label":"Passport"},
                {"value":"drivers_license","label":"Driver License"},
                {"value":"national_id","label":"National ID"},
                {"value":"other","label":"Other"}
            ]
        },
        {"label":"ID Proof Number","field_name":"id_proof_number","data_column":"id_proof_number","type_id":1,"order_index":13},
        {"label":"Reporting Manager","field_name":"reporting_manager_id","data_column":"reporting_manager_id","type_id":4,"order_index":14,
            "options_table":"users","options_label_column":"full_name","options_value_column":"user_id"},
        {"label":"Department","field_name":"department_id","data_column":"department_id","type_id":4,"order_index":15,
            "options_table":"employee_departments","options_label_column":"name","options_value_column":"id"},
        {"label":"Designation","field_name":"designation_id","data_column":"designation_id","type_id":4,"order_index":16,
            "options_table":"employee_designations","options_label_column":"name","options_value_column":"id"},
        {"label":"Joining Date","field_name":"joining_date","data_column":"joining_date","type_id":3,"order_index":17}
    ]$$,
    NULL,
    NULL
)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    mapped_table = EXCLUDED.mapped_table,
    elements = EXCLUDED.elements,
    tenant_id = EXCLUDED.tenant_id,
    branch_id = EXCLUDED.branch_id,
    updatedAt = now(),
    isDeleted = FALSE;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
    215,
    'Employment History Entry',
    'Maintain a record of employee work history entries',
    'employee_history',
    $$[
        {"label":"Employer","field_name":"employer","data_column":"employer","type_id":1,"order_index":1,"required":true},
        {"label":"Department","field_name":"department_id","data_column":"department_id","type_id":4,"order_index":2,
            "options_table":"employee_departments","options_label_column":"name","options_value_column":"id"},
        {"label":"Designation","field_name":"designation_id","data_column":"designation_id","type_id":4,"order_index":3,
            "options_table":"employee_designations","options_label_column":"name","options_value_column":"id"},
        {"label":"Start Date","field_name":"start_date","data_column":"start_date","type_id":3,"order_index":4},
        {"label":"End Date","field_name":"end_date","data_column":"end_date","type_id":3,"order_index":5},
        {"label":"Responsibilities","field_name":"responsibilities","data_column":"responsibilities","type_id":8,"order_index":6},
        {"label":"Supporting Document Id","field_name":"document_id","data_column":"document_id","type_id":1,"order_index":7},
        {"label":"Is Current Role","field_name":"is_current","data_column":"is_current","type_id":5,"order_index":8}
    ]$$,
    NULL,
    NULL
)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    mapped_table = EXCLUDED.mapped_table,
    elements = EXCLUDED.elements,
    tenant_id = EXCLUDED.tenant_id,
    branch_id = EXCLUDED.branch_id,
    updatedAt = now(),
    isDeleted = FALSE;
