-- KINET MikroTik Admin Module - Update v24 Landing Pendaftaran + Onboarding PSB
-- Jalankan sekali setelah replace file update.

CREATE TABLE IF NOT EXISTS psb_onboardings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    registration_no VARCHAR(80) NOT NULL UNIQUE,
    source VARCHAR(60) NOT NULL DEFAULT 'landing',
    name VARCHAR(160) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(160) NULL,
    address TEXT NOT NULL,
    area VARCHAR(160) NULL,
    subscription_profile_id INT NULL,
    package_name VARCHAR(160) NULL,
    service_type VARCHAR(40) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    status ENUM('new','contacted','survey','approved','rejected','converted') NOT NULL DEFAULT 'new',
    note TEXT NULL,
    admin_note TEXT NULL,
    assigned_to INT NULL,
    converted_customer_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_psb_status (status),
    INDEX idx_psb_phone (phone),
    INDEX idx_psb_profile (subscription_profile_id),
    INDEX idx_psb_created (created_at),
    CONSTRAINT fk_psb_profile FOREIGN KEY (subscription_profile_id) REFERENCES subscription_profiles(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_psb_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_psb_converted_customer FOREIGN KEY (converted_customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (setting_key, setting_value) VALUES
('landing_registration_enabled', '1'),
('landing_registration_title', 'Daftar Pemasangan Baru'),
('landing_registration_subtitle', 'Isi data singkat. Tim kami akan menghubungi Anda untuk cek coverage dan jadwal pemasangan.'),
('landing_registration_success_message', 'Pendaftaran berhasil masuk. Tim kami akan menghubungi Anda melalui WhatsApp.'),
('landing_chat_button_text', 'Chat Sekarang')
ON DUPLICATE KEY UPDATE setting_key = setting_key;