-- =============================================================================
-- Migration 034: Add debt_reminder_policies tables
-- Description: Bảng quản lý chính sách nhắc nợ linh hoạt theo nhiều điều kiện
-- Author: System
-- Date: 2026-06-02
-- =============================================================================

-- =============================================================================
-- 1. MAIN TABLE: debt_reminder_policies
-- =============================================================================

CREATE TABLE IF NOT EXISTS debt_reminder_policies (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        VARCHAR(255) NOT NULL,
    reminder_type VARCHAR(255) NOT NULL, -- 'BEFORE_DUE', 'AFTER_DUE', 'ON_DUE_DATE'
    days_offset INT NOT NULL,  -- Âm: trước hạn (-7), Dương: sau hạn (+3)
    send_time   TIME NOT NULL,  -- Giờ gửi (ví dụ: 08:00, 14:30)
    channels    JSONB NOT NULL,  -- ["APP_PUSH", "SMS", "EMAIL", "ZALO"]
    status      VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',  -- 'ACTIVE', 'INACTIVE'
    created_by  UUID NULL,
    updated_by  UUID NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes cho debt_reminder_policies
CREATE INDEX idx_debt_reminder_policies_status ON debt_reminder_policies(status);
CREATE INDEX idx_debt_reminder_policies_reminder_type ON debt_reminder_policies(reminder_type, status);
CREATE INDEX idx_debt_reminder_policies_created_at ON debt_reminder_policies(created_at DESC);

-- =============================================================================
-- 2. JUNCTION TABLE: debt_reminder_policy_targets
--
-- Thiết kế linh hoạt:
-- - 1 policy có thể áp dụng cho N targets
-- - Mỗi target có thể là: BUILDING, APARTMENT, ROOM, RESIDENT, hoặc ALL (toàn bộ)
-- - Nếu target_type = 'ALL' thì target_id = NULL
-- - Nếu muốn mix: 1 building + 2 apartments + 3 residents, tạo 6 rows
-- =============================================================================

CREATE TABLE IF NOT EXISTS debt_reminder_policy_targets (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    policy_id   UUID NOT NULL REFERENCES debt_reminder_policies(id) ON DELETE CASCADE,
    target_type VARCHAR(20) NOT NULL,  -- 'BUILDING', 'APARTMENT', 'ROOM', 'RESIDENT', 'ALL'
    target_id   UUID NULL,  -- NULL nếu target_type = 'ALL'
    created_by  UUID NULL,
    updated_by  UUID NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes cho debt_reminder_policy_targets
CREATE INDEX idx_policy_targets_policy_id ON debt_reminder_policy_targets(policy_id);
CREATE INDEX idx_policy_targets_target_type_id ON debt_reminder_policy_targets(target_type, target_id);
CREATE INDEX idx_policy_targets_target_id ON debt_reminder_policy_targets(target_id);

-- Constraint: nếu target_type != 'ALL' thì target_id không được NULL
ALTER TABLE debt_reminder_policy_targets 
ADD CONSTRAINT chk_target_id_not_null_for_specific_type 
CHECK (
    (target_type = 'ALL' AND target_id IS NULL) 
    OR (target_type != 'ALL' AND target_id IS NOT NULL)
);

-- =============================================================================
-- SEED DATA
-- =============================================================================

INSERT INTO debt_reminder_policies (name, reminder_type, days_offset, send_time, channels) VALUES

-- Policy 1: Nhắc nợ 7 ngày trước hạn
(
    'Nhắc nợ 7 ngày trước hạn',
    'BEFORE_DUE',
    -7,
    '08:00',
    '["APP_PUSH", "SMS"]'::jsonb
),

-- Policy 2: Nhắc nợ 3 ngày sau hạn
(
    'Nhắc nợ 3 ngày sau hạn',
    'AFTER_DUE',
    3,
    '09:00',
    '["APP_PUSH", "SMS", "EMAIL"]'::jsonb
),

-- Policy 3: Nhắc nợ ngay khi quá hạn
(
    'Nhắc nợ ngay khi quá hạn',
    'AFTER_DUE',
    0,
    '08:00',
    '["APP_PUSH", "SMS", "EMAIL", "ZALO"]'::jsonb
),

-- Policy 4: Nhắc nợ 14 ngày sau hạn (nhắc cuối)
(
    'Nhắc nợ cuối 14 ngày sau hạn',
    'AFTER_DUE',
    14,
    '10:00',
    '["APP_PUSH", "EMAIL"]'::jsonb
);

-- =============================================================================
-- SEED TARGETS - Áp dụng các policy cho các scope khác nhau
-- =============================================================================

-- Policy 1: Áp dụng cho TẤT CẢ buildings, apartments, residents
INSERT INTO debt_reminder_policy_targets (policy_id, target_type, target_id) 
SELECT id, 'ALL', NULL FROM debt_reminder_policies WHERE name = 'Nhắc nợ 7 ngày trước hạn';

-- Policy 2: Áp dụng cho building cụ thể (ví dụ: building_id = 'bldg-001')
-- Ghi chú: Thay thế 'bldg-001' bằng ID thực tế từ bảng buildings
-- INSERT INTO debt_reminder_policy_targets (policy_id, target_type, target_id)
-- SELECT id, 'BUILDING', 'bldg-001' FROM debt_reminder_policies WHERE name = 'Nhắc nợ 3 ngày sau hạn';

-- Policy 3: Áp dụng cho 2 apartments cụ thể
-- INSERT INTO debt_reminder_policy_targets (policy_id, target_type, target_id)
-- SELECT id, 'APARTMENT', 'apt-001' FROM debt_reminder_policies WHERE name = 'Nhắc nợ ngay khi quá hạn'
-- UNION ALL
-- SELECT id, 'APARTMENT', 'apt-002' FROM debt_reminder_policies WHERE name = 'Nhắc nợ ngay khi quá hạn';

-- Policy 4: Áp dụng cho ALL
INSERT INTO debt_reminder_policy_targets (policy_id, target_type, target_id)
SELECT id, 'ALL', NULL FROM debt_reminder_policies WHERE name = 'Nhắc nợ cuối 14 ngày sau hạn';

-- =============================================================================
-- QUERY EXAMPLES (để reference)
-- =============================================================================

/*
-- 1. Lấy tất cả active policies
SELECT * FROM debt_reminder_policies WHERE status = 'ACTIVE' ORDER BY days_offset;

-- 2. Lấy tất cả policies áp dụng cho một apartment cụ thể (apt-123)
SELECT DISTINCT p.* FROM debt_reminder_policies p
INNER JOIN debt_reminder_policy_targets t ON p.id = t.policy_id
WHERE p.status = 'ACTIVE'
AND (
    (t.target_type = 'APARTMENT' AND t.target_id = 'apt-123')
    OR (t.target_type = 'ALL')
    OR (
        t.target_type = 'BUILDING' 
        AND t.target_id = (SELECT building_id FROM apartments WHERE id = 'apt-123' LIMIT 1)
    )
)
ORDER BY p.days_offset;

-- 3. Lấy tất cả targets của một policy
SELECT * FROM debt_reminder_policy_targets WHERE policy_id = 'policy-id' ORDER BY created_at;

-- 4. Kiểm tra apartment nào được áp dụng policy BEFORE_DUE (-7 ngày)
SELECT DISTINCT a.id, a.code, a.building_id
FROM apartments a
WHERE EXISTS (
    SELECT 1 FROM debt_reminder_policies p
    INNER JOIN debt_reminder_policy_targets t ON p.id = t.policy_id
    WHERE p.status = 'ACTIVE'
    AND p.reminder_type = 'BEFORE_DUE'
    AND p.days_offset = -7
    AND (
        (t.target_type = 'APARTMENT' AND t.target_id = a.id)
        OR (t.target_type = 'BUILDING' AND t.target_id = a.building_id)
        OR (t.target_type = 'ALL')
    )
);

-- 5. Đếm số policies áp dụng cho mỗi target type
SELECT 
    t.target_type,
    COUNT(DISTINCT p.id) as policy_count,
    COUNT(DISTINCT t.target_id) as unique_targets
FROM debt_reminder_policies p
INNER JOIN debt_reminder_policy_targets t ON p.id = t.policy_id
WHERE p.status = 'ACTIVE'
GROUP BY t.target_type
ORDER BY policy_count DESC;
*/

-- =============================================================================
-- ROLLBACK SCRIPT (Comment - for reference only)
-- =============================================================================

/*
DROP TABLE IF EXISTS debt_reminder_policy_targets;
DROP TABLE IF EXISTS debt_reminder_policies;
*/

-- =============================================================================
-- END OF MIGRATION
-- =============================================================================
