BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.onboarding_checklist_templates (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    template_no VARCHAR(80) NOT NULL,
    task_name VARCHAR(180) NOT NULL,
    task_category VARCHAR(80) NOT NULL DEFAULT 'general',
    owner_role VARCHAR(120) NOT NULL DEFAULT 'HR Ops',
    due_offset_days INTEGER NOT NULL DEFAULT 0,
    mandatory BOOLEAN NOT NULL DEFAULT TRUE,
    description TEXT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'active',
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at TIMESTAMP NULL,
    deleted_by UUID NULL,
    created_by UUID NULL,
    updated_by UUID NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT onboarding_checklist_templates_no_unique UNIQUE (tenant_id, template_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.onboarding_welcome_templates (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    template_no VARCHAR(80) NOT NULL,
    template_name VARCHAR(160) NOT NULL,
    channel VARCHAR(40) NOT NULL DEFAULT 'email',
    subject VARCHAR(220) NULL,
    body TEXT NOT NULL,
    purpose VARCHAR(80) NOT NULL DEFAULT 'welcome',
    status VARCHAR(40) NOT NULL DEFAULT 'active',
    usage_count INTEGER NOT NULL DEFAULT 0,
    last_used_at TIMESTAMP NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at TIMESTAMP NULL,
    deleted_by UUID NULL,
    created_by UUID NULL,
    updated_by UUID NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT onboarding_welcome_templates_no_unique UNIQUE (tenant_id, template_no)
);

CREATE INDEX IF NOT EXISTS idx_onboarding_checklist_templates_active
    ON hr_payroll.onboarding_checklist_templates (tenant_id, status, sort_order)
    WHERE is_deleted = FALSE;

CREATE INDEX IF NOT EXISTS idx_onboarding_welcome_templates_active
    ON hr_payroll.onboarding_welcome_templates (tenant_id, status, purpose)
    WHERE is_deleted = FALSE;

INSERT INTO hr_payroll.onboarding_checklist_templates (
    tenant_id, template_no, task_name, task_category, owner_role, due_offset_days, mandatory, description, sort_order, created_at, updated_at
)
SELECT tenant_id, template_no, task_name, task_category, owner_role, due_offset_days, mandatory, description, sort_order, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM (
    SELECT DISTINCT tenant_id FROM hr_payroll.onboarding_cases
) tenants
CROSS JOIN (
    VALUES
        ('OCL-001', 'Send signed offer and appointment letter', 'documents', 'HR Ops', -14, TRUE, 'Send the signed offer and appointment letter to the pre-hire.', 10),
        ('OCL-002', 'Send welcome email and manager intro', 'communication', 'Hiring Manager', -10, TRUE, 'Introduce the manager and explain what happens before day one.', 20),
        ('OCL-003', 'Collect ID, tax, and bank documents', 'documents', 'HR Ops', -7, TRUE, 'Collect the key documents needed for employee setup and payroll.', 30),
        ('OCL-004', 'Raise IT provisioning request', 'it', 'IT', -5, TRUE, 'Request work account, email, device, and required system access.', 40),
        ('OCL-005', 'Assign onboarding buddy', 'orientation', 'Hiring Manager', -3, FALSE, 'Assign a colleague to support the new hire during their first days.', 50),
        ('OCL-006', 'Dispatch welcome kit and first-day agenda', 'welcome', 'HR Ops', -2, TRUE, 'Send first-day agenda and welcome pack details.', 60)
) defaults(template_no, task_name, task_category, owner_role, due_offset_days, mandatory, description, sort_order)
ON CONFLICT (tenant_id, template_no) DO NOTHING;

INSERT INTO hr_payroll.onboarding_welcome_templates (
    tenant_id, template_no, template_name, channel, subject, body, purpose, created_at, updated_at
)
SELECT tenant_id, template_no, template_name, channel, subject, body, purpose, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM (
    SELECT DISTINCT tenant_id FROM hr_payroll.onboarding_cases
) tenants
CROSS JOIN (
    VALUES
        ('OWT-001', 'Welcome email', 'email', 'Welcome to {{company_name}}', 'Dear {{employee_name}}, welcome to the team. We are excited to have you join as {{role_title}} on {{start_date}}.', 'welcome'),
        ('OWT-002', 'Manager intro', 'email', 'Your manager introduction', 'Dear {{employee_name}}, your manager {{manager_name}} will guide your first-week priorities and team introductions.', 'manager_intro'),
        ('OWT-003', 'First-day agenda', 'email', 'Your first-day agenda', 'Dear {{employee_name}}, here is your first-day agenda, reporting time, contacts, and setup checklist.', 'first_day')
) defaults(template_no, template_name, channel, subject, body, purpose)
ON CONFLICT (tenant_id, template_no) DO NOTHING;

COMMIT;