BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_interview_panels (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    panel_no varchar(50) NOT NULL,
    panel_name varchar(180) NOT NULL,
    recruitment_job_id bigint NULL,
    department_id bigint NULL,
    chair_user_id uuid NULL,
    members jsonb NOT NULL DEFAULT '[]'::jsonb,
    capacity_per_week integer NOT NULL DEFAULT 0,
    status varchar(40) NOT NULL DEFAULT 'active',
    notes text NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    CONSTRAINT uq_recruitment_interview_panels_no UNIQUE (tenant_id, panel_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_panel_assignments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    assignment_no varchar(50) NOT NULL,
    recruitment_interview_id bigint NULL,
    recruitment_candidate_id bigint NOT NULL,
    recruitment_job_id bigint NULL,
    recruitment_panel_id bigint NOT NULL,
    assignment_date date NULL,
    assignment_time time NULL,
    status varchar(40) NOT NULL DEFAULT 'pending',
    conflict_notes text NULL,
    confirmation_notes text NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    CONSTRAINT uq_recruitment_panel_assignments_no UNIQUE (tenant_id, assignment_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_scorecard_templates (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    template_no varchar(50) NOT NULL,
    template_name varchar(180) NOT NULL,
    recruitment_job_id bigint NULL,
    designation_id bigint NULL,
    scale varchar(40) NOT NULL DEFAULT '1-5',
    pass_threshold numeric(6, 2) NOT NULL DEFAULT 70,
    competencies jsonb NOT NULL DEFAULT '[]'::jsonb,
    status varchar(40) NOT NULL DEFAULT 'draft',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    CONSTRAINT uq_recruitment_scorecard_templates_no UNIQUE (tenant_id, template_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_interview_evaluations (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    evaluation_no varchar(50) NOT NULL,
    recruitment_interview_id bigint NOT NULL,
    recruitment_candidate_id bigint NOT NULL,
    recruitment_job_id bigint NULL,
    scorecard_template_id bigint NULL,
    evaluator_user_id uuid NULL,
    total_score numeric(6, 2) NULL,
    recommendation varchar(40) NOT NULL DEFAULT 'pending',
    competency_scores jsonb NOT NULL DEFAULT '[]'::jsonb,
    strengths text NULL,
    concerns text NULL,
    notes text NULL,
    submitted_at timestamp NULL,
    status varchar(40) NOT NULL DEFAULT 'draft',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    CONSTRAINT uq_recruitment_interview_evaluations_no UNIQUE (tenant_id, evaluation_no)
);

CREATE INDEX IF NOT EXISTS idx_recruitment_panels_tenant_status
    ON hr_payroll.recruitment_interview_panels (tenant_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_recruitment_panel_assignments_candidate
    ON hr_payroll.recruitment_panel_assignments (tenant_id, recruitment_candidate_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_recruitment_scorecards_status
    ON hr_payroll.recruitment_scorecard_templates (tenant_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_recruitment_evaluations_interview
    ON hr_payroll.recruitment_interview_evaluations (tenant_id, recruitment_interview_id, status) WHERE is_deleted = FALSE;

INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
SELECT v.module_code, v.permission_code, v.permission_name, v.description
FROM (VALUES
    ('hr-payroll', 'hr.recruitment.interviews.manage', 'Manage Interview Decision Records', 'Create and update interview panels, panel assignments, scorecards, and evaluations.'),
    ('hr-payroll', 'hr.recruitment.evaluations.submit', 'Submit Interview Evaluations', 'Submit interview evaluations and recommendations.')
) AS v(module_code, permission_code, permission_name, description)
WHERE NOT EXISTS (
    SELECT 1 FROM iam.permissions p WHERE p.permission_code = v.permission_code
);

INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM iam.roles r
JOIN iam.permissions p ON p.permission_code IN ('hr.recruitment.interviews.manage', 'hr.recruitment.evaluations.submit')
WHERE LOWER(COALESCE(r.code, '')) IN ('tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator', 'hr_manager', 'hr-admin', 'hr_admin')
  AND NOT EXISTS (
      SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

COMMIT;