BEGIN;

CREATE SCHEMA IF NOT EXISTS hr_payroll;

ALTER TABLE IF EXISTS hr_payroll.disciplinary_cases
    ADD COLUMN IF NOT EXISTS reported_by_user_id uuid,
    ADD COLUMN IF NOT EXISTS acknowledged_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS acknowledged_by uuid,
    ADD COLUMN IF NOT EXISTS hearing_scheduled_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS hearing_minutes text,
    ADD COLUMN IF NOT EXISTS decision_reason text,
    ADD COLUMN IF NOT EXISTS appeal_reason text,
    ADD COLUMN IF NOT EXISTS appeal_outcome text,
    ADD COLUMN IF NOT EXISTS closed_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS closed_by uuid,
    ADD COLUMN IF NOT EXISTS outcome varchar(80),
    ADD COLUMN IF NOT EXISTS employee_name varchar(220),
    ADD COLUMN IF NOT EXISTS investigator_name varchar(220),
    ADD COLUMN IF NOT EXISTS witnesses jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS evidence_metadata jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS workflow_audit jsonb NOT NULL DEFAULT '[]'::jsonb;

CREATE TABLE IF NOT EXISTS hr_payroll.employee_grievances (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    grievance_no varchar(80) NOT NULL,
    employee_id bigint NOT NULL REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    employee_name varchar(220),
    reported_by_user_id uuid,
    grievance_type varchar(80) NOT NULL DEFAULT 'workplace',
    severity varchar(40) NOT NULL DEFAULT 'medium',
    reported_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acknowledged_at timestamp without time zone,
    acknowledged_by uuid,
    assigned_to_user_id uuid,
    assigned_to_name varchar(220),
    subject varchar(220) NOT NULL,
    description text,
    desired_resolution text,
    status varchar(40) NOT NULL DEFAULT 'reported',
    resolution_summary text,
    resolved_at timestamp without time zone,
    resolved_by uuid,
    closed_at timestamp without time zone,
    closed_by uuid,
    witnesses jsonb NOT NULL DEFAULT '[]'::jsonb,
    evidence_metadata jsonb NOT NULL DEFAULT '[]'::jsonb,
    workflow_audit jsonb NOT NULL DEFAULT '[]'::jsonb,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_by uuid,
    updated_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT uq_employee_grievances_no UNIQUE (tenant_id, grievance_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_relations_investigations (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    investigation_no varchar(80) NOT NULL,
    source_type varchar(80) NOT NULL DEFAULT 'grievance',
    grievance_id bigint REFERENCES hr_payroll.employee_grievances(id),
    disciplinary_case_id bigint REFERENCES hr_payroll.disciplinary_cases(id),
    employee_id bigint REFERENCES hr_payroll.employees(id),
    employee_name varchar(220),
    investigator_user_id uuid,
    investigator_name varchar(220),
    opened_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    due_at timestamp without time zone,
    completed_at timestamp without time zone,
    risk_level varchar(40) NOT NULL DEFAULT 'medium',
    status varchar(40) NOT NULL DEFAULT 'open',
    allegation_summary text,
    findings text,
    recommendation text,
    witnesses jsonb NOT NULL DEFAULT '[]'::jsonb,
    evidence_metadata jsonb NOT NULL DEFAULT '[]'::jsonb,
    workflow_audit jsonb NOT NULL DEFAULT '[]'::jsonb,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_by uuid,
    updated_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT uq_employee_relations_investigations_no UNIQUE (tenant_id, investigation_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_relations_actions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    action_no varchar(80) NOT NULL,
    action_type varchar(80) NOT NULL DEFAULT 'corrective_action',
    employee_id bigint NOT NULL REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    employee_name varchar(220),
    grievance_id bigint REFERENCES hr_payroll.employee_grievances(id),
    disciplinary_case_id bigint REFERENCES hr_payroll.disciplinary_cases(id),
    investigation_id bigint REFERENCES hr_payroll.employee_relations_investigations(id),
    severity varchar(40) NOT NULL DEFAULT 'medium',
    effective_date date,
    expires_at date,
    status varchar(40) NOT NULL DEFAULT 'draft',
    action_summary text NOT NULL,
    decision_reason text,
    appeal_status varchar(40),
    appeal_reason text,
    appeal_outcome text,
    approved_by uuid,
    approved_at timestamp without time zone,
    completed_by uuid,
    completed_at timestamp without time zone,
    evidence_metadata jsonb NOT NULL DEFAULT '[]'::jsonb,
    workflow_audit jsonb NOT NULL DEFAULT '[]'::jsonb,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_by uuid,
    updated_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT uq_employee_relations_actions_no UNIQUE (tenant_id, action_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_relations_case_events (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    source_type varchar(80) NOT NULL,
    source_id bigint NOT NULL,
    event_type varchar(80) NOT NULL,
    event_summary text,
    event_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actor_user_id uuid,
    actor_name varchar(220),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_hr_grievances_employee ON hr_payroll.employee_grievances (tenant_id, employee_id, reported_at DESC) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_hr_grievances_status ON hr_payroll.employee_grievances (tenant_id, status, severity) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_hr_er_investigations_status ON hr_payroll.employee_relations_investigations (tenant_id, status, risk_level) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_hr_er_actions_employee ON hr_payroll.employee_relations_actions (tenant_id, employee_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_hr_er_events_source ON hr_payroll.employee_relations_case_events (tenant_id, source_type, source_id, event_at DESC);

WITH permissions_to_seed(module_code, permission_code, permission_name, description) AS (
    VALUES
        ('hr-payroll', 'hr.employee_relations.view', 'View Employee Relations', 'Allows viewing grievances, investigations, disciplinary cases, and employee relations actions.'),
        ('hr-payroll', 'hr.employee_relations.create', 'Create Employee Relations Records', 'Allows creating grievances, investigations, evidence, and employee relations actions.'),
        ('hr-payroll', 'hr.employee_relations.update', 'Update Employee Relations Records', 'Allows updating case details, evidence, status, and workflow records.'),
        ('hr-payroll', 'hr.employee_relations.approve', 'Approve Employee Relations Actions', 'Allows acknowledging, deciding, approving, and closing employee relations cases.'),
        ('hr-payroll', 'hr.employee_relations.delete', 'Delete Employee Relations Records', 'Allows removing employee relations records where policy allows.')
),
inserted_permissions AS (
    INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
    SELECT module_code, permission_code, permission_name, description
    FROM permissions_to_seed seed
    WHERE NOT EXISTS (
        SELECT 1 FROM iam.permissions existing WHERE existing.permission_code = seed.permission_code
    )
    RETURNING id, permission_code
),
all_permissions AS (
    SELECT id, permission_code FROM inserted_permissions
    UNION
    SELECT p.id, p.permission_code
    FROM iam.permissions p
    INNER JOIN permissions_to_seed seed ON seed.permission_code = p.permission_code
),
privileged_roles AS (
    SELECT id
    FROM iam.roles
    WHERE COALESCE(is_active, TRUE) = TRUE
      AND LOWER(REPLACE(COALESCE(code, name, ''), ' ', '_')) IN (
          'tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator',
          'hr_admin', 'hr-admin', 'hr_manager', 'human_resources_manager',
          'employee_relations_officer', 'employee-relations-officer'
      )
)
INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT pr.id, ap.id
FROM privileged_roles pr
CROSS JOIN all_permissions ap
WHERE NOT EXISTS (
    SELECT 1
    FROM iam.role_permissions existing
    WHERE existing.role_id = pr.id
      AND existing.permission_id = ap.id
);

COMMIT;