-- ============================================================
--  Ten08 – Betting Prediction Platform Migration
--  Run this once against your MySQL database.
--  Engine: MyISAM (no transaction support required/used).
-- ============================================================

-- ──────────────────────────────────────────────────────────
--  admins  (Admin portal staff / super admins)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admins` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`           VARCHAR(255)    NOT NULL,
    `email`          VARCHAR(255)    NOT NULL,
    `password`       VARCHAR(255)    NOT NULL,
    `phone_number`   VARCHAR(30)     DEFAULT NULL,
    `is_super_admin` ENUM('Yes','No') NOT NULL DEFAULT 'No',
    `status`         ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    `last_login`     DATETIME        DEFAULT NULL,
    `date_created`   DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_admin_email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  customers  (Customer / mobile app accounts)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `customers` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`           VARCHAR(255)    NOT NULL,
    `email`          VARCHAR(255)    NOT NULL,
    `phone_number`   VARCHAR(30)     DEFAULT NULL,
    `password`       VARCHAR(255)    NOT NULL,
    `current_plan`   ENUM('Free','Pro') NOT NULL DEFAULT 'Free',
    `status`         ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    `last_login`     DATETIME        DEFAULT NULL,
    `date_created`   DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_customer_email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  matches  (Individual match data & prediction stats)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `matches` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `home_team`         VARCHAR(255)    NOT NULL,
    `away_team`         VARCHAR(255)    NOT NULL,
    `league`            VARCHAR(255)    DEFAULT NULL,
    `match_date`        DATE            NOT NULL,
    `match_time`        TIME            DEFAULT NULL,
    `prediction`        VARCHAR(255)    DEFAULT NULL,
    `odds`              DECIMAL(6,2)    DEFAULT NULL,
    `prediction_result` ENUM('Pending','Won','Loss') NOT NULL DEFAULT 'Pending',
    `home_score`        VARCHAR(10)     DEFAULT NULL,
    `away_score`        VARCHAR(10)     DEFAULT NULL,
    `status`            ENUM('Active','Deleted') NOT NULL DEFAULT 'Active',
    `created_by`        BIGINT UNSIGNED NOT NULL,
    `date_created`      DATETIME        NOT NULL,
    `updated_at`        DATETIME        DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_match_date`   (`match_date`),
    KEY `idx_match_result` (`prediction_result`),
    KEY `idx_match_status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  bet_slips  (Groups of matches)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `bet_slips` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title`        VARCHAR(255)    NOT NULL,
    `description`  TEXT            DEFAULT NULL,
    `total_odds`   DECIMAL(8,2)    DEFAULT NULL,
    `date_time`    DATETIME        NOT NULL,
    `status`       ENUM('Active','Deleted') NOT NULL DEFAULT 'Active',
    `created_by`   BIGINT UNSIGNED NOT NULL,
    `date_created` DATETIME        NOT NULL,
    `updated_at`   DATETIME        DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_slip_datetime` (`date_time`),
    KEY `idx_slip_status`   (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  betslip_matches  (Maps matches into a bet slip)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `betslip_matches` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `betslip_id`   BIGINT UNSIGNED NOT NULL,
    `match_id`     BIGINT UNSIGNED NOT NULL,
    `date_created` DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_bm_slip`  (`betslip_id`),
    KEY `idx_bm_match` (`match_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  login_sessions  (reused from Atlas — Admin/Customer rows use
--  company_id = 0 as a sentinel since this portal is not multi-tenant)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `login_sessions` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id`       BIGINT UNSIGNED NOT NULL,
    `user_type`        VARCHAR(50)     NOT NULL DEFAULT 'Employee',
    `user_id`          BIGINT UNSIGNED NOT NULL,
    `device_id`        VARCHAR(255)    NOT NULL DEFAULT '',
    `device_name`      VARCHAR(255)    NOT NULL DEFAULT '',
    `token`            VARCHAR(500)    NOT NULL DEFAULT '',
    `user_ses_id`      VARCHAR(255)    NOT NULL,
    `mode`             VARCHAR(50)     NOT NULL DEFAULT 'app',
    `status`           VARCHAR(20)     NOT NULL DEFAULT 'active',
    `login_date_time`  DATETIME        NOT NULL,
    `logout_date_time` DATETIME                 DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_ls_user` (`user_id`, `user_type`),
    KEY `idx_ls_ses`  (`user_ses_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  otp_tokens  (Verification / password reset tokens)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `otp_tokens` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_type`     ENUM('Admin','Customer') NOT NULL DEFAULT 'Customer',
    `user_id`       BIGINT UNSIGNED NOT NULL,
    `token`         VARCHAR(4)      NOT NULL,
    `context`       VARCHAR(100)    NOT NULL DEFAULT 'reset_password',
    `status`        ENUM('Active','Used','Expired') NOT NULL DEFAULT 'Active',
    `date_expired`  DATETIME        NOT NULL,
    `date_created`  DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_otp_user` (`user_id`, `user_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  customer_notifications  (In-app customer alerts.
--  Named distinctly from the existing `notifications` table,
--  which is already used by the Atlas employee portal)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `customer_notifications` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_id`  BIGINT UNSIGNED NOT NULL,
    `title`        VARCHAR(255)    NOT NULL,
    `message`      TEXT            NOT NULL,
    `type`         VARCHAR(50)     NOT NULL DEFAULT 'general',
    `type_id`      BIGINT UNSIGNED DEFAULT NULL,
    `is_read`      ENUM('Yes','No') NOT NULL DEFAULT 'No',
    `read_at`      DATETIME        DEFAULT NULL,
    `date_created` DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_cn_customer` (`customer_id`, `is_read`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  rollover & rollover_matches  (Rollover campaign tracking)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `rollover` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title`          VARCHAR(255)    NOT NULL,
    `description`    TEXT            DEFAULT NULL,
    `starting_stake` DECIMAL(10,2)   DEFAULT NULL,
    `current_day`    INT UNSIGNED    NOT NULL DEFAULT 1,
    `total_days`     INT UNSIGNED    NOT NULL DEFAULT 1,
    `status`         ENUM('Active','Completed','Failed','Deleted') NOT NULL DEFAULT 'Active',
    `created_by`     BIGINT UNSIGNED NOT NULL,
    `date_created`   DATETIME        NOT NULL,
    `updated_at`     DATETIME        DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `rollover_matches` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `rollover_id`  BIGINT UNSIGNED NOT NULL,
    `match_id`     BIGINT UNSIGNED NOT NULL,
    `day_number`   INT UNSIGNED    NOT NULL DEFAULT 1,
    `date_created` DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_rm_rollover` (`rollover_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  Admin log tables
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admin_initial_report` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_type`     VARCHAR(100)    DEFAULT NULL,
    `transaction_id`  VARCHAR(255)    DEFAULT NULL,
    `server_request`  LONGTEXT        DEFAULT NULL,
    `date_time`       DATETIME        NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `admin_successful_report` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_type`     VARCHAR(100)    DEFAULT NULL,
    `admin_id`        BIGINT UNSIGNED DEFAULT NULL,
    `transaction_id`  VARCHAR(255)    DEFAULT NULL,
    `server_request`  LONGTEXT        DEFAULT NULL,
    `server_response` LONGTEXT        DEFAULT NULL,
    `date_time`       DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_admin_transaction` (`transaction_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `admin_error_report` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_type`     VARCHAR(100)    DEFAULT NULL,
    `description`     TEXT            DEFAULT NULL,
    `transaction_id`  VARCHAR(255)    DEFAULT NULL,
    `server_request`  LONGTEXT        DEFAULT NULL,
    `date_time`       DATETIME        NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  Customer log tables
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `customer_initial_report` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_type`     VARCHAR(100)    DEFAULT NULL,
    `transaction_id`  VARCHAR(255)    DEFAULT NULL,
    `server_request`  LONGTEXT        DEFAULT NULL,
    `date_time`       DATETIME        NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `customer_successful_report` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_type`     VARCHAR(100)    DEFAULT NULL,
    `customer_id`     BIGINT UNSIGNED DEFAULT NULL,
    `transaction_id`  VARCHAR(255)    DEFAULT NULL,
    `server_request`  LONGTEXT        DEFAULT NULL,
    `server_response` LONGTEXT        DEFAULT NULL,
    `date_time`       DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_customer_transaction` (`transaction_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `customer_error_report` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_type`     VARCHAR(100)    DEFAULT NULL,
    `description`     TEXT            DEFAULT NULL,
    `transaction_id`  VARCHAR(255)    DEFAULT NULL,
    `server_request`  LONGTEXT        DEFAULT NULL,
    `date_time`       DATETIME        NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;