BEGIN;

CREATE SCHEMA IF NOT EXISTS hr_payroll;

ALTER TABLE IF EXISTS hr_payroll.training_programs
    ADD COLUMN IF NOT EXISTS program_type varchar(80),
    ADD COLUMN IF NOT EXISTS mandatory boolean NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS linked_performance_review_id bigint,
    ADD COLUMN IF NOT EXISTS linked_onboarding_task_id bigint,
    ADD COLUMN IF NOT EXISTS completion_required_for_role boolean NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

CREATE TABLE IF NOT EXISTS hr_payroll.training_courses (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    course_no varchar(50) NOT NULL,
    course_name varchar(180) NOT NULL,
    training_program_id bigint,
    category varchar(100),
    delivery_mode varchar(50) NOT NULL DEFAULT 'classroom',
    duration_hours numeric(10, 2) NOT NULL DEFAULT 0,
    pass_score numeric(6, 2),
    certificate_valid_months integer,
    provider_name varchar(180),
    description text,
    status varchar(40) NOT NULL DEFAULT 'active',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT fk_training_courses_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_training_courses_program FOREIGN KEY (training_program_id) REFERENCES hr_payroll.training_programs(id) ON DELETE SET NULL,
    CONSTRAINT uq_training_courses_no UNIQUE (tenant_id, course_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.training_sessions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    session_no varchar(50) NOT NULL,
    training_program_id bigint,
    training_course_id bigint,
    session_name varchar(180) NOT NULL,
    facilitator varchar(180),
    location_label varchar(180),
    starts_at timestamp without time zone,
    ends_at timestamp without time zone,
    capacity integer,
    status varchar(40) NOT NULL DEFAULT 'scheduled',
    notes text,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT fk_training_sessions_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_training_sessions_program FOREIGN KEY (training_program_id) REFERENCES hr_payroll.training_programs(id) ON DELETE SET NULL,
    CONSTRAINT fk_training_sessions_course FOREIGN KEY (training_course_id) REFERENCES hr_payroll.training_courses(id) ON DELETE SET NULL,
    CONSTRAINT uq_training_sessions_no UNIQUE (tenant_id, session_no)
);

ALTER TABLE IF EXISTS hr_payroll.employee_training_enrollments
    ADD COLUMN IF NOT EXISTS enrollment_no varchar(50),
    ADD COLUMN IF NOT EXISTS training_course_id bigint,
    ADD COLUMN IF NOT EXISTS training_session_id bigint,
    ADD COLUMN IF NOT EXISTS attendance_status varchar(40) NOT NULL DEFAULT 'not_recorded',
    ADD COLUMN IF NOT EXISTS completion_status varchar(40) NOT NULL DEFAULT 'not_started',
    ADD COLUMN IF NOT EXISTS certificate_no varchar(80),
    ADD COLUMN IF NOT EXISTS certificate_expires_at date,
    ADD COLUMN IF NOT EXISTS source_type varchar(80),
    ADD COLUMN IF NOT EXISTS source_id bigint,
    ADD COLUMN IF NOT EXISTS is_required boolean NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS is_active boolean NOT NULL DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS deleted_by uuid,
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'employee_training_enrollments_course_fkey'
    ) THEN
        ALTER TABLE hr_payroll.employee_training_enrollments
            ADD CONSTRAINT employee_training_enrollments_course_fkey FOREIGN KEY (training_course_id) REFERENCES hr_payroll.training_courses(id) ON DELETE SET NULL;
    END IF;
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'employee_training_enrollments_session_fkey'
    ) THEN
        ALTER TABLE hr_payroll.employee_training_enrollments
            ADD CONSTRAINT employee_training_enrollments_session_fkey FOREIGN KEY (training_session_id) REFERENCES hr_payroll.training_sessions(id) ON DELETE SET NULL;
    END IF;
END $$;

UPDATE hr_payroll.employee_training_enrollments
SET enrollment_no = 'TRN-ENR-' || id::text
WHERE enrollment_no IS NULL OR btrim(enrollment_no) = '';

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'uq_employee_training_enrollments_no'
    ) THEN
        ALTER TABLE hr_payroll.employee_training_enrollments
            ADD CONSTRAINT uq_employee_training_enrollments_no UNIQUE (tenant_id, enrollment_no);
    END IF;
END $$;

CREATE TABLE IF NOT EXISTS hr_payroll.employee_training_certifications (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    certification_no varchar(50) NOT NULL,
    employee_id bigint NOT NULL,
    training_program_id bigint,
    training_course_id bigint,
    training_enrollment_id bigint,
    certification_name varchar(180) NOT NULL,
    issuer varchar(180),
    issued_at date,
    expires_at date,
    status varchar(40) NOT NULL DEFAULT 'active',
    score numeric(6, 2),
    document_reference varchar(255),
    notes text,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT fk_training_certifications_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_training_certifications_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_training_certifications_program FOREIGN KEY (training_program_id) REFERENCES hr_payroll.training_programs(id) ON DELETE SET NULL,
    CONSTRAINT fk_training_certifications_course FOREIGN KEY (training_course_id) REFERENCES hr_payroll.training_courses(id) ON DELETE SET NULL,
    CONSTRAINT fk_training_certifications_enrollment FOREIGN KEY (training_enrollment_id) REFERENCES hr_payroll.employee_training_enrollments(id) ON DELETE SET NULL,
    CONSTRAINT uq_training_certifications_no UNIQUE (tenant_id, certification_no)
);

CREATE INDEX IF NOT EXISTS idx_training_courses_tenant ON hr_payroll.training_courses (tenant_id, status, course_name) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_training_sessions_tenant ON hr_payroll.training_sessions (tenant_id, status, starts_at) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_training_enrollments_course ON hr_payroll.employee_training_enrollments (tenant_id, training_course_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_training_enrollments_employee ON hr_payroll.employee_training_enrollments (tenant_id, employee_id, completion_status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_training_certifications_employee ON hr_payroll.employee_training_certifications (tenant_id, employee_id, expires_at) WHERE is_deleted = FALSE;

WITH desired_permissions(module_code, permission_code, permission_name, description) AS (
    VALUES
        ('hr-payroll', 'hr.training.view', 'View Training', 'Allows viewing training programs, courses, sessions, enrollments, and certifications.'),
        ('hr-payroll', 'hr.training.create', 'Create Training', 'Allows creating training programs, courses, sessions, enrollments, and certifications.'),
        ('hr-payroll', 'hr.training.update', 'Update Training', 'Allows updating training programs, courses, sessions, enrollments, and certifications.'),
        ('hr-payroll', 'hr.training.delete', 'Delete Training', 'Allows deleting or archiving training records.'),
        ('hr-payroll', 'hr.training.complete', 'Complete Training', 'Allows recording training attendance, completion, scores, and certificates.')
)
INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
SELECT dp.module_code, dp.permission_code, dp.permission_name, dp.description
FROM desired_permissions dp
WHERE NOT EXISTS (
    SELECT 1 FROM iam.permissions p WHERE p.permission_code = dp.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.training.view',
    'hr.training.create',
    'hr.training.update',
    'hr.training.delete',
    'hr.training.complete'
)
WHERE LOWER(COALESCE(r.code, '')) IN ('tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator', 'hr_manager', 'hr-admin', 'hr_admin', 'training_coordinator', 'training-coordinator')
  AND NOT EXISTS (
      SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

COMMIT;