-- WASeen Server Database
-- Run this on your MySQL server

CREATE DATABASE IF NOT EXISTS waseen_engine;
USE waseen_engine;

-- ============================================================
-- 1. devices_viewer (iPhone)
-- ============================================================
CREATE TABLE devices_viewer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL UNIQUE,
    apns_token VARCHAR(255) DEFAULT NULL,
    user_type ENUM('free','plus','pro_p1','pro_p2') DEFAULT 'free',
    plan_expiry DATETIME DEFAULT NULL,
    invite_code VARCHAR(20) DEFAULT NULL UNIQUE,
    referred_by VARCHAR(20) DEFAULT NULL,
    country_code VARCHAR(10) DEFAULT '',
    app_version VARCHAR(20) DEFAULT '1.0.0',
    last_seen DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_viewer_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 2. devices_engine (Android / Mac / Windows)
-- ============================================================
CREATE TABLE devices_engine (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL UNIQUE,
    platform ENUM('android','mac','windows') NOT NULL,
    push_token VARCHAR(255) DEFAULT NULL,
    engine_version VARCHAR(20) DEFAULT '1.0.0',
    last_heartbeat DATETIME DEFAULT NULL,
    is_active TINYINT DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_engine_heartbeat (last_heartbeat),
    INDEX idx_engine_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. wa_accounts (WhatsApp accounts on engine)
-- ============================================================
CREATE TABLE wa_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    engine_device_id INT NOT NULL,
    wa_phone VARCHAR(20) NOT NULL UNIQUE,
    status ENUM('connected','disconnected','logged_out') DEFAULT 'disconnected',
    config_version INT DEFAULT 0,
    connected_at DATETIME DEFAULT NULL,
    last_active DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (engine_device_id) REFERENCES devices_engine(id) ON DELETE CASCADE,
    INDEX idx_wa_engine (engine_device_id),
    INDEX idx_wa_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 4. pairings (iPhone <-> WA account)
-- ============================================================
CREATE TABLE pairings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    wa_account_id INT NOT NULL,
    viewer_device_id INT DEFAULT NULL,
    pair_code VARCHAR(10) NOT NULL UNIQUE,
    pair_qr_data VARCHAR(500) DEFAULT NULL,
    status ENUM('pending','active','revoked') DEFAULT 'pending',
    paired_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wa_account_id) REFERENCES wa_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (viewer_device_id) REFERENCES devices_viewer(id) ON DELETE SET NULL,
    INDEX idx_pair_status (status),
    INDEX idx_pair_viewer (viewer_device_id),
    INDEX idx_pair_wa (wa_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 5. tracked_contacts
-- ============================================================
CREATE TABLE tracked_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    wa_account_id INT NOT NULL,
    added_by_device_id INT NOT NULL,
    phone VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    notify_online TINYINT DEFAULT 1,
    notify_offline TINYINT DEFAULT 0,
    online_sound VARCHAR(50) DEFAULT NULL,
    offline_sound VARCHAR(50) DEFAULT NULL,
    last_status ENUM('available','unavailable') DEFAULT NULL,
    last_seen DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wa_account_id) REFERENCES wa_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by_device_id) REFERENCES devices_viewer(id) ON DELETE CASCADE,
    UNIQUE KEY uk_contact (wa_account_id, added_by_device_id, phone),
    INDEX idx_tc_wa (wa_account_id),
    INDEX idx_tc_viewer (added_by_device_id),
    INDEX idx_tc_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 6. auto_reply_rules
-- ============================================================
CREATE TABLE auto_reply_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    wa_account_id INT NOT NULL,
    added_by_device_id INT NOT NULL,
    rule_id VARCHAR(50) NOT NULL UNIQUE,
    keyword TEXT NOT NULL,
    reply TEXT NOT NULL,
    match_type ENUM('exact','contains','startswith','all') DEFAULT 'contains',
    target_phone VARCHAR(20) DEFAULT NULL,
    enabled TINYINT DEFAULT 1,
    start_time VARCHAR(5) DEFAULT NULL,
    end_time VARCHAR(5) DEFAULT NULL,
    cooldown_min INT DEFAULT 0,
    attachment_path VARCHAR(255) DEFAULT NULL,
    attachment_type VARCHAR(20) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wa_account_id) REFERENCES wa_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by_device_id) REFERENCES devices_viewer(id) ON DELETE CASCADE,
    INDEX idx_rule_wa (wa_account_id),
    INDEX idx_rule_viewer (added_by_device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 7. presence_log
-- ============================================================
CREATE TABLE presence_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    wa_account_id INT NOT NULL,
    phone VARCHAR(20) NOT NULL,
    name VARCHAR(100) DEFAULT '',
    status ENUM('available','unavailable') NOT NULL,
    timestamp DATETIME NOT NULL,
    FOREIGN KEY (wa_account_id) REFERENCES wa_accounts(id) ON DELETE CASCADE,
    INDEX idx_plog_wa_phone (wa_account_id, phone, timestamp DESC),
    INDEX idx_plog_ts (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 8. message_log
-- ============================================================
CREATE TABLE message_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    wa_account_id INT NOT NULL,
    phone VARCHAR(20) NOT NULL,
    name VARCHAR(100) DEFAULT '',
    text TEXT,
    auto_replied TINYINT DEFAULT 0,
    rule_id VARCHAR(50) DEFAULT NULL,
    timestamp DATETIME NOT NULL,
    FOREIGN KEY (wa_account_id) REFERENCES wa_accounts(id) ON DELETE CASCADE,
    INDEX idx_mlog_wa (wa_account_id, timestamp DESC),
    INDEX idx_mlog_ts (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
