-- ============================================================================
-- GADAI MULIA - COLLECTION BROADCAST TO SOPIGA COLLAR
-- SIMPLIFIED SCHEMA: Denormalized broadcast_staging
-- ============================================================================

CREATE SCHEMA IF NOT EXISTS collection_broadcasts;

-- ============================================================================
-- 1. CONFIGURATION TABLES (Reference only)
-- ============================================================================

-- Sopiga Template Config (pre-setup di Sopiga)
CREATE TABLE collection_broadcasts.sopiga_template_config (
  id BIGSERIAL PRIMARY KEY,
  template_name VARCHAR(100) NOT NULL,
  sopiga_template_id INT NOT NULL UNIQUE,
  template_type VARCHAR(50),                    -- utility, marketing, notification
  channel VARCHAR(50),                          -- waba
  description TEXT,
  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_sopiga_template_active ON collection_broadcasts.sopiga_template_config(active);

-- Template Variable Mapping (untuk dynamic message building)
CREATE TABLE collection_broadcasts.template_variable_mapping (
  id BIGSERIAL PRIMARY KEY,
  sopiga_template_id INT NOT NULL REFERENCES collection_broadcasts.sopiga_template_config(sopiga_template_id) ON DELETE CASCADE,
  variable_order INT NOT NULL,                  -- 1, 2, 3, ... (urutan interpolasi)
  sopiga_variable_name VARCHAR(100) NOT NULL,   -- "Nama", "TotalTagihan", "NoKontrak", etc
  variable_type VARCHAR(50),                    -- string, integer, date, decimal
  db_field_source VARCHAR(100) NOT NULL,        -- Key di message_payload
  is_required BOOLEAN DEFAULT TRUE,
  example_value VARCHAR(500),
  description TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  
  CONSTRAINT unique_template_variable UNIQUE (sopiga_template_id, sopiga_variable_name),
  CONSTRAINT unique_variable_order UNIQUE (sopiga_template_id, variable_order)
);

CREATE INDEX idx_template_variable_mapping_template_id ON collection_broadcasts.template_variable_mapping(sopiga_template_id);
CREATE INDEX idx_template_variable_mapping_order ON collection_broadcasts.template_variable_mapping(sopiga_template_id, variable_order);

-- Sopiga Collar Config (pre-created di Sopiga)
CREATE TABLE collection_broadcasts.sopiga_collar_config (
  id BIGSERIAL PRIMARY KEY,
  broadcast_name VARCHAR(100) NOT NULL,
  sopiga_collar_id INT NOT NULL UNIQUE,
  sopiga_template_id INT NOT NULL REFERENCES collection_broadcasts.sopiga_template_config(sopiga_template_id),
  status VARCHAR(50),                           -- open, closed
  description TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_sopiga_collar_status ON collection_broadcasts.sopiga_collar_config(status);

-- ============================================================================
-- 2. BROADCAST STAGING (CORE) - DENORMALIZED
-- ============================================================================

-- Single source of truth: message_payload (JSONB)
-- Semua data sudah ada di sini, tidak perlu FK ke nasabah/contract
-- Worker query hanya dari tabel ini
CREATE TABLE collection_broadcasts.broadcast_staging (
  id BIGSERIAL PRIMARY KEY,
  
  -- Sopiga config
  sopiga_collar_id INT NOT NULL REFERENCES collection_broadcasts.sopiga_collar_config(sopiga_collar_id),
  sopiga_template_id INT NOT NULL REFERENCES collection_broadcasts.sopiga_template_config(sopiga_template_id),
  
  -- Message payload - SINGLE SOURCE OF TRUTH
  -- Contains all fields needed: nasabah_nama, nasabah_phone, nominal_tagihan, tanggal_tempo, invoice_pdf_url, contract_no, etc
  -- Struktur dinamis sesuai template variables
  -- Example: {
  --   "nasabah_nama": "Budi Santoso",
  --   "nasabah_phone": "6281234567890",
  --   "nominal_tagihan": 1500000,
  --   "tanggal_tempo": "2026-06-30",
  --   "invoice_pdf_url": "https://storage.gadai.com/invoices/inv-001.pdf",
  --   "contract_no": "GAD-2026-001"
  -- }
  message_payload JSONB NOT NULL,
  
  -- Status tracking
  status VARCHAR(50) DEFAULT 'pending' NOT NULL,
    -- pending: waiting to dispatch
    -- dispatched: sent to Sopiga queue (HTTP 202 Accepted)
    -- delivered: confirmed delivered to WhatsApp
    -- failed: error occurred
    -- retry_scheduled: queued for retry
  error_message TEXT,
  error_count INT DEFAULT 0,
  
  -- Sopiga response
  sopiga_recipient_detail_id BIGINT,
  sopiga_external_id VARCHAR(100),
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  dispatched_at TIMESTAMP,
  delivered_at TIMESTAMP,
  failed_at TIMESTAMP,
  
  CONSTRAINT status_valid CHECK (status IN ('pending', 'dispatched', 'delivered', 'failed', 'retry_scheduled')),
  CONSTRAINT error_count_positive CHECK (error_count >= 0)
);

-- Indexes untuk worker & monitoring queries
CREATE INDEX idx_broadcast_staging_status ON collection_broadcasts.broadcast_staging(status);
CREATE INDEX idx_broadcast_staging_created_at ON collection_broadcasts.broadcast_staging(created_at DESC);
CREATE INDEX idx_broadcast_staging_collar_id ON collection_broadcasts.broadcast_staging(sopiga_collar_id);
CREATE INDEX idx_broadcast_staging_template_id ON collection_broadcasts.broadcast_staging(sopiga_template_id);

-- Composite index untuk worker (most frequent query)
CREATE INDEX idx_broadcast_staging_pending_query 
  ON collection_broadcasts.broadcast_staging(status, created_at ASC)
  WHERE status = 'pending';

-- Composite index untuk retry queue
CREATE INDEX idx_broadcast_staging_retry_query 
  ON collection_broadcasts.broadcast_staging(status, error_count, created_at ASC)
  WHERE status = 'retry_scheduled' AND error_count < 3;

-- ============================================================================
-- 3. AUDIT & LOGGING
-- ============================================================================

-- Status transition audit log
CREATE TABLE collection_broadcasts.broadcast_audit_log (
  id BIGSERIAL PRIMARY KEY,
  broadcast_id BIGINT NOT NULL REFERENCES collection_broadcasts.broadcast_staging(id) ON DELETE CASCADE,
  old_status VARCHAR(50),
  new_status VARCHAR(50) NOT NULL,
  reason VARCHAR(500),
  sopiga_response JSONB,
  changed_by VARCHAR(100) DEFAULT 'system',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_broadcast_audit_log_broadcast_id ON collection_broadcasts.broadcast_audit_log(broadcast_id);
CREATE INDEX idx_broadcast_audit_log_created_at ON collection_broadcasts.broadcast_audit_log(created_at DESC);

-- Error tracking
CREATE TABLE collection_broadcasts.broadcast_error_log (
  id BIGSERIAL PRIMARY KEY,
  broadcast_id BIGINT NOT NULL REFERENCES collection_broadcasts.broadcast_staging(id) ON DELETE CASCADE,
  error_type VARCHAR(100),                      -- network_error, api_error, validation_error
  error_code VARCHAR(50),
  error_message TEXT,
  error_details JSONB,
  attempt_number INT,
  next_retry_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_broadcast_error_log_broadcast_id ON collection_broadcasts.broadcast_error_log(broadcast_id);
CREATE INDEX idx_broadcast_error_log_error_type ON collection_broadcasts.broadcast_error_log(error_type);

-- Sopiga delivery status sync
CREATE TABLE collection_broadcasts.sopiga_sync_job (
  id BIGSERIAL PRIMARY KEY,
  broadcast_id BIGINT NOT NULL REFERENCES collection_broadcasts.broadcast_staging(id) ON DELETE CASCADE,
  sopiga_recipient_detail_id BIGINT,
  last_synced_at TIMESTAMP,
  last_status_from_sopiga VARCHAR(50),
  sync_count INT DEFAULT 0,
  next_sync_at TIMESTAMP,
  completed_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_sopiga_sync_job_broadcast_id ON collection_broadcasts.sopiga_sync_job(broadcast_id);
CREATE INDEX idx_sopiga_sync_job_next_sync_at ON collection_broadcasts.sopiga_sync_job(next_sync_at);

-- ============================================================================
-- 4. VIEWS
-- ============================================================================

-- Template variables dengan urutan (untuk Worker reference)
CREATE VIEW collection_broadcasts.v_template_variables_ordered AS
SELECT 
  tvm.sopiga_template_id,
  stc.template_name,
  tvm.variable_order,
  tvm.sopiga_variable_name,
  tvm.variable_type,
  tvm.db_field_source,
  tvm.is_required,
  tvm.example_value
FROM collection_broadcasts.template_variable_mapping tvm
JOIN collection_broadcasts.sopiga_template_config stc ON tvm.sopiga_template_id = stc.sopiga_template_id
WHERE stc.active = TRUE
ORDER BY tvm.sopiga_template_id, tvm.variable_order;

-- Collar summary
CREATE VIEW collection_broadcasts.v_collar_summary AS
SELECT 
  sc.sopiga_collar_id,
  sc.broadcast_name,
  COUNT(*) as total_records,
  COUNT(CASE WHEN bs.status = 'pending' THEN 1 END) as pending,
  COUNT(CASE WHEN bs.status = 'dispatched' THEN 1 END) as dispatched,
  COUNT(CASE WHEN bs.status = 'delivered' THEN 1 END) as delivered,
  COUNT(CASE WHEN bs.status = 'failed' THEN 1 END) as failed,
  ROUND(100.0 * COUNT(CASE WHEN bs.status = 'delivered' THEN 1 END) / NULLIF(COUNT(*), 0), 2) as delivery_rate_percent
FROM collection_broadcasts.sopiga_collar_config sc
LEFT JOIN collection_broadcasts.broadcast_staging bs ON sc.sopiga_collar_id = bs.sopiga_collar_id
GROUP BY sc.sopiga_collar_id, sc.broadcast_name;

-- Failed records (last 24h)
CREATE VIEW collection_broadcasts.v_failed_records_24h AS
SELECT 
  id,
  sopiga_collar_id,
  sopiga_template_id,
  message_payload->>'nasabah_nama' as nasabah_nama,
  message_payload->>'nasabah_phone' as nasabah_phone,
  error_message,
  error_count,
  failed_at,
  created_at
FROM collection_broadcasts.broadcast_staging
WHERE status = 'failed' AND created_at > NOW() - INTERVAL 1 DAY
ORDER BY failed_at DESC;

-- Delivery rate (last 24h)
CREATE VIEW collection_broadcasts.v_delivery_rate_24h AS
SELECT 
  COUNT(*) as total,
  COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered,
  COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed,
  COUNT(CASE WHEN status = 'dispatched' THEN 1 END) as in_progress,
  ROUND(100.0 * COUNT(CASE WHEN status = 'delivered' THEN 1 END) / NULLIF(COUNT(*), 0), 2) as delivery_rate_percent
FROM collection_broadcasts.broadcast_staging
WHERE created_at > NOW() - INTERVAL 1 DAY;

-- ============================================================================
-- 5. HELPER FUNCTIONS
-- ============================================================================

-- Get template variables by template_id
CREATE OR REPLACE FUNCTION collection_broadcasts.get_template_variables(
  p_sopiga_template_id INT
)
RETURNS TABLE(
  variable_order INT,
  sopiga_variable_name VARCHAR,
  variable_type VARCHAR,
  db_field_source VARCHAR,
  is_required BOOLEAN
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    tvm.variable_order,
    tvm.sopiga_variable_name,
    tvm.variable_type,
    tvm.db_field_source,
    tvm.is_required
  FROM collection_broadcasts.template_variable_mapping tvm
  WHERE tvm.sopiga_template_id = p_sopiga_template_id
  ORDER BY tvm.variable_order ASC;
END;
$$ LANGUAGE plpgsql;

-- Update status dengan audit log
CREATE OR REPLACE FUNCTION collection_broadcasts.update_broadcast_status(
  p_broadcast_id BIGINT,
  p_new_status VARCHAR,
  p_error_message TEXT DEFAULT NULL,
  p_sopiga_response JSONB DEFAULT NULL
)
RETURNS VOID AS $$
DECLARE
  v_old_status VARCHAR;
BEGIN
  SELECT status INTO v_old_status
  FROM collection_broadcasts.broadcast_staging
  WHERE id = p_broadcast_id;

  UPDATE collection_broadcasts.broadcast_staging
  SET 
    status = p_new_status,
    error_message = p_error_message,
    updated_at = NOW(),
    dispatched_at = CASE WHEN p_new_status = 'dispatched' THEN NOW() ELSE dispatched_at END,
    delivered_at = CASE WHEN p_new_status = 'delivered' THEN NOW() ELSE delivered_at END,
    failed_at = CASE WHEN p_new_status = 'failed' THEN NOW() ELSE failed_at END
  WHERE id = p_broadcast_id;

  INSERT INTO collection_broadcasts.broadcast_audit_log (broadcast_id, old_status, new_status, sopiga_response)
  VALUES (p_broadcast_id, v_old_status, p_new_status, p_sopiga_response);
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- 6. INITIALIZATION DATA
-- ============================================================================

-- Insert sample template
INSERT INTO collection_broadcasts.sopiga_template_config (template_name, sopiga_template_id, channel, template_type, description)
VALUES ('Collection Invoice May 2026', 2, 'waba', 'utility', 'Invoice bulanan untuk collection Mei 2026');

-- Insert template variables
INSERT INTO collection_broadcasts.template_variable_mapping (sopiga_template_id, variable_order, sopiga_variable_name, variable_type, db_field_source, is_required, example_value)
VALUES
  (2, 1, 'Nama', 'string', 'nasabah_nama', TRUE, 'Budi Santoso'),
  (2, 2, 'TotalTagihan', 'integer', 'nominal_tagihan', TRUE, '1500000'),
  (2, 3, 'TanggalJatuhTempo', 'date', 'tanggal_tempo', TRUE, '2026-06-30');

-- Insert collar config
INSERT INTO collection_broadcasts.sopiga_collar_config (broadcast_name, sopiga_collar_id, sopiga_template_id, status, description)
VALUES ('Collection Invoices May 2026', 70, 2, 'open', 'Broadcast collar untuk collection invoice bulanan Mei 2026');