/*****************************************************************
 * PROJECT      : Ezi2Iman_bot
 * MODULE       : Database
 * FILE         : sql/schema.sql
 * VERSION      : v1.1.0
 * BUILD        : 2026.07.28.002
 * STATUS       : Ready to Run
 * ACTION       : Import into MySQL database
 * DESCRIPTION  : Initial database schema for Telegram prayer and hadith bot.
 *****************************************************************/

SET NAMES utf8mb4;
SET time_zone = '+08:00';

CREATE TABLE IF NOT EXISTS prayer_zones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zone_code VARCHAR(20) NOT NULL UNIQUE,
    state_name VARCHAR(100) NOT NULL,
    zone_name VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bot_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_user_id VARCHAR(50) NOT NULL UNIQUE,
    telegram_chat_id VARCHAR(50) NOT NULL,
    first_name VARCHAR(150) NULL,
    username VARCHAR(150) NULL,
    zone_code VARCHAR(20) NOT NULL DEFAULT 'PNG01',
    schedule_enabled TINYINT(1) NOT NULL DEFAULT 1,
    prayer_reminder_enabled TINYINT(1) NOT NULL DEFAULT 1,
    hadith_enabled TINYINT(1) NOT NULL DEFAULT 1,
    friday_enabled TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('active','blocked','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_bot_users_chat (telegram_chat_id),
    INDEX idx_bot_users_zone (zone_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS prayer_times (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zone_code VARCHAR(20) NOT NULL,
    prayer_date DATE NOT NULL,
    hijri_date VARCHAR(100) NULL,
    imsak TIME NULL,
    subuh TIME NULL,
    syuruk TIME NULL,
    dhuha TIME NULL,
    zohor TIME NULL,
    asar TIME NULL,
    maghrib TIME NULL,
    isyak TIME NULL,
    source_name VARCHAR(100) NOT NULL DEFAULT 'JAKIM e-Solat',
    fetched_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_prayer_zone_date (zone_code, prayer_date),
    INDEX idx_prayer_date (prayer_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hadiths (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(100) NULL,
    arabic_text TEXT NULL,
    malay_text TEXT NOT NULL,
    source_reference VARCHAR(255) NOT NULL,
    authenticity VARCHAR(100) NOT NULL,
    lesson TEXT NULL,
    scheduled_date DATE NULL,
    status ENUM('draft','review','approved','rejected') NOT NULL DEFAULT 'draft',
    approved_by BIGINT UNSIGNED NULL,
    approved_at DATETIME NULL,
    last_sent_at DATETIME NULL,
    sent_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_hadith_status (status),
    INDEX idx_hadith_schedule (scheduled_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_chat_id VARCHAR(50) NOT NULL,
    message_type VARCHAR(50) NOT NULL,
    related_id BIGINT UNSIGNED NULL,
    status ENUM('sent','failed','skipped') NOT NULL,
    error_message TEXT NULL,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_message_logs_chat (telegram_chat_id),
    INDEX idx_message_logs_type (message_type),
    INDEX idx_message_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO prayer_zones (zone_code, state_name, zone_name) VALUES
('PNG01', 'Pulau Pinang', 'Seluruh Negeri Pulau Pinang'),
('KDH01', 'Kedah', 'Kota Setar, Kubang Pasu, Pokok Sena'),
('KDH02', 'Kedah', 'Pendang, Kuala Muda, Yan'),
('KDH03', 'Kedah', 'Padang Terap, Sik'),
('KDH04', 'Kedah', 'Baling'),
('KDH05', 'Kedah', 'Kulim, Bandar Baharu'),
('KDH06', 'Kedah', 'Langkawi'),
('PRK01', 'Perak', 'Tapah, Slim River, Tanjung Malim'),
('PRK02', 'Perak', 'Ipoh, Batu Gajah, Kampar, Sungai Siput, Kuala Kangsar'),
('PRK03', 'Perak', 'Pengkalan Hulu, Grik, Lenggong'),
('PRK04', 'Perak', 'Temengor, Belum'),
('PRK05', 'Perak', 'Teluk Intan, Bagan Datuk, Kampung Gajah'),
('PRK06', 'Perak', 'Selama, Taiping, Bagan Serai, Parit Buntar'),
('PRK07', 'Perak', 'Bukit Larut'),
('PLS01', 'Perlis', 'Seluruh Negeri Perlis')
ON DUPLICATE KEY UPDATE zone_name=VALUES(zone_name), state_name=VALUES(state_name);

INSERT INTO hadiths (category, arabic_text, malay_text, source_reference, authenticity, lesson, status, approved_at) VALUES
('Niat', 'إِنَّمَا الأَعْمَالُ بِالنِّيَّاتِ', 'Sesungguhnya setiap amalan bergantung kepada niat.', 'Riwayat al-Bukhari dan Muslim', 'Sahih', 'Perbetulkan niat supaya setiap urusan yang baik menjadi ibadah.', 'approved', NOW());