SET NAMES utf8mb4;
SET time_zone = '+05:30';

CREATE TABLE wa_agents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('ADMIN','SUPERVISOR','AGENT') NOT NULL DEFAULT 'AGENT',
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    availability ENUM('ONLINE','AWAY','OFFLINE') NOT NULL DEFAULT 'OFFLINE',
    last_seen_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_agent_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wa_contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wa_id VARCHAR(30) NOT NULL,
    mobile VARCHAR(20) NOT NULL,
    profile_name VARCHAR(150) NULL,
    huber_id BIGINT NULL,
    status ENUM('ACTIVE','BLOCKED') NOT NULL DEFAULT 'ACTIVE',
    first_message_at DATETIME NULL,
    last_message_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_contact_wa_id (wa_id),
    KEY idx_contact_mobile (mobile),
    KEY idx_contact_huber (huber_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wa_conversations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contact_id BIGINT UNSIGNED NOT NULL,
    assigned_agent_id BIGINT UNSIGNED NULL,
    status ENUM('UNASSIGNED','OPEN','PENDING','RESOLVED','CLOSED') NOT NULL DEFAULT 'UNASSIGNED',
    priority ENUM('LOW','NORMAL','HIGH','URGENT') NOT NULL DEFAULT 'NORMAL',
    chatbot_enabled TINYINT(1) NOT NULL DEFAULT 1,
    last_message_preview VARCHAR(500) NULL,
    last_message_type VARCHAR(30) NULL,
    last_message_at DATETIME NULL,
    last_customer_message_at DATETIME NULL,
    last_agent_message_at DATETIME NULL,
    unread_count INT UNSIGNED NOT NULL DEFAULT 0,
    opened_at DATETIME NULL,
    resolved_at DATETIME NULL,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_conversation_contact FOREIGN KEY (contact_id) REFERENCES wa_contacts(id),
    CONSTRAINT fk_conversation_agent FOREIGN KEY (assigned_agent_id) REFERENCES wa_agents(id) ON DELETE SET NULL,
    KEY idx_conversation_queue (status, assigned_agent_id, last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wa_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NOT NULL,
    agent_id BIGINT UNSIGNED NULL,
    wamid VARCHAR(255) NULL,
    reply_to_wamid VARCHAR(255) NULL,
    direction ENUM('INBOUND','OUTBOUND') NOT NULL,
    sender_type ENUM('CUSTOMER','AGENT','BOT','SYSTEM') NOT NULL,
    message_type ENUM('TEXT','IMAGE','VIDEO','AUDIO','DOCUMENT','STICKER','LOCATION','CONTACTS','INTERACTIVE','TEMPLATE','REACTION','UNKNOWN') NOT NULL DEFAULT 'TEXT',
    message_text LONGTEXT NULL,
    media_id VARCHAR(255) NULL,
    media_mime_type VARCHAR(150) NULL,
    media_filename VARCHAR(255) NULL,
    media_caption TEXT NULL,
    payload_json LONGTEXT NULL,
    status ENUM('RECEIVED','QUEUED','SENT','DELIVERED','READ','FAILED','DELETED') NOT NULL DEFAULT 'RECEIVED',
    error_code VARCHAR(100) NULL,
    error_message TEXT NULL,
    message_timestamp DATETIME NULL,
    sent_at DATETIME NULL,
    delivered_at DATETIME NULL,
    read_at DATETIME NULL,
    failed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_message_wamid (wamid),
    KEY idx_message_conversation (conversation_id, id),
    CONSTRAINT fk_message_conversation FOREIGN KEY (conversation_id) REFERENCES wa_conversations(id),
    CONSTRAINT fk_message_contact FOREIGN KEY (contact_id) REFERENCES wa_contacts(id),
    CONSTRAINT fk_message_agent FOREIGN KEY (agent_id) REFERENCES wa_agents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wa_webhook_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_hash CHAR(64) NOT NULL,
    payload_json LONGTEXT NOT NULL,
    processed_at DATETIME NULL,
    processing_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_webhook_hash (event_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wa_internal_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    agent_id BIGINT UNSIGNED NOT NULL,
    note TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_note_conversation FOREIGN KEY (conversation_id) REFERENCES wa_conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_note_agent FOREIGN KEY (agent_id) REFERENCES wa_agents(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;