-- Telegram Unified Inbox v2 — Userbot (akaun biasa) edition
-- Import ini dalam phpMyAdmin.

CREATE TABLE IF NOT EXISTS telegram_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(100) NOT NULL,               -- nama paparan, cth "EA Robot Support 1"
    phone_number VARCHAR(20) NOT NULL,
    session_name VARCHAR(50) NOT NULL UNIQUE,  -- nama folder session, cth "acc1" (huruf/nombor je)
    telegram_user_id BIGINT DEFAULT NULL,
    badge_color VARCHAR(7) NOT NULL DEFAULT '#2AABEE',
    is_logged_in TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,   -- off kan sementara tanpa padam akaun
    last_update_id BIGINT NOT NULL DEFAULT 0,  -- offset untuk getUpdates()
    last_polled_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    customer_chat_id BIGINT NOT NULL,
    customer_first_name VARCHAR(150) DEFAULT NULL,
    customer_last_name VARCHAR(150) DEFAULT NULL,
    customer_username VARCHAR(100) DEFAULT NULL,
    last_message_preview VARCHAR(255) DEFAULT NULL,
    last_message_at DATETIME DEFAULT NULL,
    unread_count INT NOT NULL DEFAULT 0,
    tag VARCHAR(50) DEFAULT NULL,
    is_pinned TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uniq_account_chat (account_id, customer_chat_id),
    FOREIGN KEY (account_id) REFERENCES telegram_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT NOT NULL,
    telegram_message_id BIGINT DEFAULT NULL,
    direction ENUM('in','out') NOT NULL,
    status ENUM('received','queued','sent','failed') NOT NULL DEFAULT 'received',
    body TEXT DEFAULT NULL,
    media_type VARCHAR(30) DEFAULT NULL,
    media_file_id VARCHAR(255) DEFAULT NULL,
    sent_by VARCHAR(100) DEFAULT NULL,
    sent_at DATETIME NOT NULL,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','staff') NOT NULL DEFAULT 'staff',
    created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
    token VARCHAR(64) PRIMARY KEY,
    agent_id INT NOT NULL,
    expires_at DATETIME NOT NULL,
    FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS canned_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    category VARCHAR(50) DEFAULT NULL,
    created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_conv_last_msg ON conversations(last_message_at);
CREATE INDEX idx_msg_conv ON messages(conversation_id, sent_at);
CREATE INDEX idx_msg_status ON messages(status);