-- =============================================================================
-- Migration 016: Extract contract_type to separate table
-- Purpose: Normalize contract_type from ENUM to a reference table
--          Allows dynamic management of contract types without schema changes
-- Tables: contract_types (new)
-- Modified: contracts (add contract_type_id FK, keep contract_type for backward compatibility)
-- =============================================================================

BEGIN;

-- =============================================================================
-- 1. CREATE contract_types TABLE
-- =============================================================================

CREATE TABLE IF NOT EXISTS contract_types (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    
    -- Type info
    code VARCHAR(50) NOT NULL UNIQUE,           -- residential_lease, room_rental, etc.
    name_vi VARCHAR(100) NOT NULL,              -- Tên tiếng Việt
    name_en VARCHAR(100),                       -- Tên tiếng Anh
    description TEXT,                           -- Mô tả chi tiết
    
    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,
    
    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT contract_types_code_not_empty CHECK (length(trim(code)) > 0),
    CONSTRAINT contract_types_name_vi_not_empty CHECK (length(trim(name_vi)) > 0),
    CONSTRAINT contract_types_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR (deleted_at IS NOT NULL)
    )
);

-- Add index on code for faster lookups
CREATE INDEX idx_contract_types_code ON contract_types(code);

-- =============================================================================
-- 2. INSERT DEFAULT CONTRACT TYPES
-- =============================================================================

INSERT INTO contract_types (code, name_vi, name_en, description) VALUES
    ('residential_lease', 'Thuê nhà/căn hộ', 'Residential Lease', 'Hợp đồng thuê toàn bộ nhà hoặc căn hộ'),
    ('room_rental', 'Thuê phòng', 'Room Rental', 'Hợp đồng thuê phòng đơn lẻ'),
    ('service_agreement', 'Hợp đồng dịch vụ', 'Service Agreement', 'Hợp đồng cung cấp dịch vụ'),
    ('other', 'Khác', 'Other', 'Các loại hợp đồng khác')
ON CONFLICT (code) DO NOTHING;

-- =============================================================================
-- 3. ADD contract_type_id COLUMN TO contracts TABLE
-- =============================================================================

ALTER TABLE contracts
ADD COLUMN IF NOT EXISTS contract_type_id UUID 
REFERENCES contract_types(id) ON DELETE RESTRICT;

-- =============================================================================
-- 4. MIGRATE EXISTING DATA FROM contract_type ENUM TO contract_type_id
-- =============================================================================

-- Populate contract_type_id from existing contract_type ENUM values for all records
UPDATE contracts c
SET contract_type_id = ct.id
FROM contract_types ct
WHERE c.contract_type::text = ct.code
  AND c.contract_type_id IS NULL;

-- Log any records that couldn't be migrated
-- SELECT id, contract_type::text, contract_type_id, deleted_at
-- FROM contracts 
-- WHERE contract_type_id IS NULL;

-- For records with unmatched contract_type values, assign to 'other' type
-- (This shouldn't happen if migration is clean, but handles edge cases)
UPDATE contracts c
SET contract_type_id = ct.id
FROM contract_types ct
WHERE ct.code = 'other'
  AND c.contract_type_id IS NULL;

-- =============================================================================
-- 5. ADD NOT NULL CONSTRAINT TO contract_type_id (after data migration)
-- =============================================================================

ALTER TABLE contracts
ALTER COLUMN contract_type_id SET NOT NULL;

-- =============================================================================
-- 6. ADD INDEXES FOR PERFORMANCE
-- =============================================================================

CREATE INDEX IF NOT EXISTS idx_contracts_contract_type_id 
ON contracts(contract_type_id);

-- =============================================================================
-- 7. ADD update_updated_at_column TRIGGER
-- =============================================================================

-- contract_types needs update_updated_at_column trigger for auto-updating updated_at
DROP TRIGGER IF EXISTS update_contract_types_updated_at ON contract_types;

CREATE TRIGGER update_contract_types_updated_at
BEFORE UPDATE ON contract_types
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- =============================================================================
-- 8. DROP OLD contract_type ENUM COLUMN FROM contracts TABLE
-- =============================================================================

-- Drop old index on contract_type
DROP INDEX IF EXISTS idx_contracts_type;

-- Drop old contract_type ENUM column
ALTER TABLE contracts
DROP COLUMN IF EXISTS contract_type;

-- Drop ENUM type if no longer used elsewhere
DROP TYPE IF EXISTS contract_type;

COMMIT;
