-- ============================================================
-- rider_schema.sql — Rider Onboarding System DB tables
-- Run this once after setting up the database.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Ensure the database itself uses utf8mb4
-- (replace khabarko_riderbot with your actual DB name if different)
-- ALTER DATABASE khabarko_riderbot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ── Admin users ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS admin_users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    last_login    DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin (change password on first login!)
INSERT IGNORE INTO admin_users (username, password_hash)
VALUES ('admin', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi.');
-- Default password: "password"

-- ── Login attempts ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS login_attempts (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    ip_address   VARCHAR(45) NOT NULL,
    attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Blocked IPs ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS blocked_ips (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL UNIQUE,
    reason     TEXT,
    blocked_by INT,
    blocked_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Audit log ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    admin_id         INT,
    action           VARCHAR(100) NOT NULL,
    target_rider_id  INT,
    details          TEXT,
    ip_address       VARCHAR(45),
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_rider (target_rider_id),
    INDEX idx_admin (admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Riders ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS riders (
    id                       INT AUTO_INCREMENT PRIMARY KEY,
    telegram_chat_id         BIGINT NOT NULL UNIQUE,
    full_name                VARCHAR(255) NOT NULL,
    mobile                   VARCHAR(20)  NOT NULL,
    present_address          TEXT         NOT NULL,
    permanent_address        TEXT         NOT NULL,
    has_vehicle              ENUM('bike','cycle','none') NOT NULL,
    vehicle_type             VARCHAR(100),
    has_driving_license      ENUM('yes','no') NOT NULL,
    experience               TEXT,
    guarantor_name           VARCHAR(255),
    guarantor_relation       ENUM('mother','father','brother','sister','other'),
    guarantor_relation_other VARCHAR(100),
    status                   ENUM('pending','approved','rejected') DEFAULT 'pending',
    rejection_reason         TEXT,
    login_email              VARCHAR(255),
    login_password_hash      VARCHAR(255),
    submitted_at             DATETIME DEFAULT CURRENT_TIMESTAMP,
    reviewed_at              DATETIME,
    reviewed_by              INT,
    INDEX idx_status (status),
    INDEX idx_chat   (telegram_chat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Rider Sessions ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS rider_sessions (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    chat_id      BIGINT NOT NULL UNIQUE,
    current_step TINYINT DEFAULT 1,
    temp_data    JSON,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Rider Documents ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS rider_documents (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    rider_id          INT NOT NULL,
    doc_type          ENUM(
                          'nid',
                          'birth_certificate',
                          'driving_license',
                          'vehicle_photo',
                          'guarantor_nid',
                          'chairman_certificate'
                      ) NOT NULL,
    original_filename VARCHAR(255),
    stored_filename   VARCHAR(255) NOT NULL,
    mime_type         VARCHAR(100),
    uploaded_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (rider_id) REFERENCES riders(id) ON DELETE CASCADE,
    INDEX idx_rider (rider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Rider Agreements ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS rider_agreements (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    rider_id            INT NOT NULL UNIQUE,
    agreement_html      MEDIUMTEXT,
    agreement_pdf_path  VARCHAR(500),
    signed_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (rider_id) REFERENCES riders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Settings (key-value store, includes agreement template) ──────────────
CREATE TABLE IF NOT EXISTS settings (
    setting_key   VARCHAR(100) NOT NULL PRIMARY KEY,
    setting_value MEDIUMTEXT,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- FIX for already-existing tables created without utf8mb4:
-- Run this block if the tables already exist with wrong charset.
-- ============================================================

ALTER TABLE riders
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE rider_sessions
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE rider_documents
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE rider_agreements
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE audit_logs
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ============================================================
-- MIGRATION: rename login_username → login_email
-- Run only if the table was created before this change.
-- ============================================================

ALTER TABLE riders
    CHANGE COLUMN login_username login_email VARCHAR(255) DEFAULT NULL;

-- ============================================================
-- MIGRATION: create settings table if it doesn't exist yet.
-- (Safe to run multiple times — IF NOT EXISTS.)
-- ============================================================

CREATE TABLE IF NOT EXISTS settings (
    setting_key   VARCHAR(100) NOT NULL PRIMARY KEY,
    setting_value MEDIUMTEXT,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;