CREATE TABLE IF NOT EXISTS hr_payroll.statutory_filings (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    filing_no VARCHAR(50) NOT NULL,
    payroll_run_id BIGINT NOT NULL REFERENCES hr_payroll.payroll_runs(id),
    filing_type VARCHAR(30) NOT NULL,
    authority_code VARCHAR(30) NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    due_date DATE NULL,
    currency_code VARCHAR(3) NOT NULL DEFAULT 'GHS',
    employee_count INTEGER NOT NULL DEFAULT 0,
    taxable_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    employee_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    employer_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    total_payable NUMERIC(18,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    validation_status VARCHAR(20) NOT NULL DEFAULT 'not_validated',
    validation_errors JSONB NOT NULL DEFAULT '[]'::jsonb,
    validated_at TIMESTAMPTZ NULL,
    requested_by UUID NULL,
    submitted_for_approval_at TIMESTAMPTZ NULL,
    approved_by UUID NULL,
    approved_at TIMESTAMPTZ NULL,
    rejected_by UUID NULL,
    rejected_at TIMESTAMPTZ NULL,
    rejection_reason TEXT NULL,
    generated_by UUID NULL,
    generated_at TIMESTAMPTZ NULL,
    file_key VARCHAR(500) NULL,
    file_name VARCHAR(255) NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT NULL,
    sha256_checksum CHAR(64) NULL,
    download_count INTEGER NOT NULL DEFAULT 0,
    last_downloaded_at TIMESTAMPTZ NULL,
    submission_method VARCHAR(30) NULL,
    submission_reference VARCHAR(160) NULL,
    submitted_by UUID NULL,
    submitted_at TIMESTAMPTZ NULL,
    receipt_reference VARCHAR(160) NULL,
    receipt_file_reference VARCHAR(500) NULL,
    authority_status VARCHAR(30) NULL,
    authority_response TEXT NULL,
    authority_decided_at TIMESTAMPTZ NULL,
    payment_reference VARCHAR(160) NULL,
    payment_amount NUMERIC(18,2) NULL,
    payment_date DATE NULL,
    paid_by UUID NULL,
    paid_at TIMESTAMPTZ NULL,
    accounting_journal_id BIGINT NULL REFERENCES accounting.journals(id) ON DELETE RESTRICT,
    reconciliation_status VARCHAR(30) NOT NULL DEFAULT 'unreconciled',
    reconciled_at TIMESTAMPTZ NULL,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID NULL,
    updated_by UUID NULL,
    CONSTRAINT uq_statutory_filings_tenant_no UNIQUE (tenant_id, filing_no),
    CONSTRAINT uq_statutory_filings_run_type UNIQUE (tenant_id, payroll_run_id, filing_type),
    CONSTRAINT ck_statutory_filings_type CHECK (filing_type IN ('paye','ssnit_tier1','pension_tier2')),
    CONSTRAINT ck_statutory_filings_status CHECK (status IN ('draft','pending_approval','approved','rejected','generated','submitted','accepted','authority_rejected','paid')),
    CONSTRAINT ck_statutory_filings_validation CHECK (validation_status IN ('not_validated','valid','invalid')),
    CONSTRAINT ck_statutory_filings_reconciliation CHECK (reconciliation_status IN ('unreconciled','matched','variance')),
    CONSTRAINT ck_statutory_filings_checksum CHECK (sha256_checksum IS NULL OR sha256_checksum ~ '^[0-9a-f]{64}$')
);

CREATE TABLE IF NOT EXISTS hr_payroll.statutory_filing_items (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    statutory_filing_id BIGINT NOT NULL REFERENCES hr_payroll.statutory_filings(id) ON DELETE RESTRICT,
    statutory_contribution_record_id BIGINT NOT NULL REFERENCES hr_payroll.statutory_contribution_records(id) ON DELETE RESTRICT,
    employee_id BIGINT NOT NULL,
    taxable_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    employee_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    employer_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    snapshot JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID NULL,
    CONSTRAINT uq_statutory_filing_items_contribution UNIQUE (tenant_id, statutory_filing_id, statutory_contribution_record_id)
);

CREATE INDEX IF NOT EXISTS idx_statutory_filings_tenant_period ON hr_payroll.statutory_filings (tenant_id, period_end DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_statutory_filing_items_filing ON hr_payroll.statutory_filing_items (tenant_id, statutory_filing_id, employee_id);

DROP TRIGGER IF EXISTS trg_statutory_filings_payroll_audit ON hr_payroll.statutory_filings;
CREATE TRIGGER trg_statutory_filings_payroll_audit AFTER INSERT OR UPDATE OR DELETE ON hr_payroll.statutory_filings
FOR EACH ROW EXECUTE FUNCTION hr_payroll.capture_payroll_audit_event();

DROP TRIGGER IF EXISTS trg_statutory_filing_items_payroll_audit ON hr_payroll.statutory_filing_items;
CREATE TRIGGER trg_statutory_filing_items_payroll_audit AFTER INSERT ON hr_payroll.statutory_filing_items
FOR EACH ROW EXECUTE FUNCTION hr_payroll.capture_payroll_audit_event();

INSERT INTO iam.permissions (module_code, permission_code, permission_name, description) VALUES
('hr-payroll','hr.statutory.filings.view','View Statutory Filings','View statutory filing records and evidence.'),
('hr-payroll','hr.statutory.filings.create','Create Statutory Filing Drafts','Create period statutory filing drafts.'),
('hr-payroll','hr.statutory.filings.approve','Approve Statutory Filings','Approve or reject statutory filings.'),
('hr-payroll','hr.statutory.filings.submit','Submit Statutory Filings','Generate files and record authority submissions.'),
('hr-payroll','hr.statutory.filings.pay','Record Statutory Payments','Record and reconcile statutory payments.')
ON CONFLICT (permission_code) DO UPDATE SET permission_name=EXCLUDED.permission_name, description=EXCLUDED.description;

INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT r.id,p.id FROM iam.roles r CROSS JOIN iam.permissions p
WHERE p.permission_code IN ('hr.statutory.filings.view','hr.statutory.filings.create','hr.statutory.filings.approve','hr.statutory.filings.submit','hr.statutory.filings.pay')
AND LOWER(COALESCE(r.code,r.name,'')) IN ('tenant_owner','tenant-owner','owner','administrator','admin','payroll_manager','finance_manager','auditor','internal_auditor')
AND NOT EXISTS (SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id);