-- Migration: Add Program Access Control System
-- Date: 2026-03-28
-- Description: Adds access type to program table and creates program_access_user_map table

-- Step 1: Create enum types
DO $$ BEGIN
    CREATE TYPE program_access_type_enum AS ENUM ('PUBLIC', 'INTERNAL', 'RESTRICTED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE access_scope_enum AS ENUM ('VIEW_ONLY', 'VIEW_AND_REGISTER');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE access_state_enum AS ENUM ('ACTIVE', 'REMOVED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Step 2: Add access control columns to program_v1 table
ALTER TABLE program_v1 
ADD COLUMN IF NOT EXISTS access_type program_access_type_enum NOT NULL DEFAULT 'PUBLIC';

-- Add comment on columns
COMMENT ON COLUMN program_v1.access_type IS 'Defines program access control: PUBLIC (all can view), INTERNAL (only mapped users), RESTRICTED (published but only mapped users)';

-- Step 3: Create program_access_user_map table
CREATE TABLE IF NOT EXISTS program_access_user_map (
    id SERIAL PRIMARY KEY,
    program_id INTEGER NOT NULL REFERENCES program_v1(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    access_scope access_scope_enum NOT NULL DEFAULT 'VIEW_AND_REGISTER',
    program_status program_status_enum NULL,
    state access_state_enum NOT NULL DEFAULT 'ACTIVE',
    effective_from TIMESTAMPTZ NULL,
    effective_till TIMESTAMPTZ NULL,
    reason TEXT NULL,
    meta JSONB NULL,
    created_by INTEGER NOT NULL REFERENCES users(id),
    updated_by INTEGER NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ NULL
);

-- Add comments on table and columns
COMMENT ON TABLE program_access_user_map IS 'Tracks which users have access to view/register for specific programs with audit trail';
COMMENT ON COLUMN program_access_user_map.access_scope IS 'Defines what level of access the user has - VIEW_ONLY or VIEW_AND_REGISTER';
COMMENT ON COLUMN program_access_user_map.program_status IS 'Status of the program at the time of mapping';
COMMENT ON COLUMN program_access_user_map.state IS 'Current state of the access mapping - ACTIVE or REMOVED';
COMMENT ON COLUMN program_access_user_map.effective_from IS 'When the access becomes effective. NULL means effective immediately';
COMMENT ON COLUMN program_access_user_map.effective_till IS 'When the access expires. NULL means no expiration';
COMMENT ON COLUMN program_access_user_map.reason IS 'Reason for granting or modifying access';
COMMENT ON COLUMN program_access_user_map.meta IS 'Additional metadata for the mapping';
COMMENT ON COLUMN program_access_user_map.deleted_at IS 'Soft delete timestamp';

-- Step 4: Create indexes
CREATE INDEX IF NOT EXISTS idx_program_access_program_state_deleted 
ON program_access_user_map(program_id, state, deleted_at);

CREATE INDEX IF NOT EXISTS idx_program_access_user_state_deleted 
ON program_access_user_map(user_id, state, deleted_at);

CREATE INDEX IF NOT EXISTS idx_program_access_effective_dates 
ON program_access_user_map(effective_from, effective_till)
WHERE deleted_at IS NULL AND state = 'ACTIVE';

-- Step 5: Create partial unique index for active mappings
CREATE UNIQUE INDEX IF NOT EXISTS idx_program_access_unique_active 
ON program_access_user_map(program_id, user_id, state)
WHERE deleted_at IS NULL AND state = 'ACTIVE';
