-- =====================================================
-- GLO Token System — Database Schema
-- Import this via cPanel phpMyAdmin
-- =====================================================

CREATE TABLE IF NOT EXISTS devices (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    android_id      VARCHAR(64)  NOT NULL UNIQUE,
    token_balance   INT          NOT NULL DEFAULT 0,
    last_free_token DATETIME     NULL,
    total_earned    INT          NOT NULL DEFAULT 0,
    total_used      INT          NOT NULL DEFAULT 0,
    next_shortener_index INT     NOT NULL DEFAULT 1,
    status          ENUM('active','banned') NOT NULL DEFAULT 'active',
    first_seen_ip   VARCHAR(45)  NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_android_id (android_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shorteners (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    seq_order   INT          NOT NULL,
    url         VARCHAR(500) NOT NULL,
    label       VARCHAR(100) NULL,
    enabled     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_seq (seq_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tasks (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    task_id             VARCHAR(16)  NOT NULL UNIQUE,
    android_id          VARCHAR(64)  NOT NULL,
    shortener_id        INT          NOT NULL,
    shortener_number    INT          NOT NULL,
    request_ip          VARCHAR(45)  NULL,
    claim_ip            VARCHAR(45)  NULL,
    minimum_wait        INT          NOT NULL DEFAULT 20,
    status              ENUM('pending','landed','claimed','expired') NOT NULL DEFAULT 'pending',
    landed_at           DATETIME     NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    claimed_at          DATETIME     NULL,
    expires_at          DATETIME     NOT NULL,
    INDEX idx_task_id (task_id),
    INDEX idx_android_status (android_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS token_transactions (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    android_id  VARCHAR(64)  NOT NULL,
    type        ENUM('free_daily','shortener_earn','create_spend','admin_adjust') NOT NULL,
    amount      INT          NOT NULL,
    task_id     VARCHAR(16)  NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_android (android_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
    setting_key   VARCHAR(64) NOT NULL PRIMARY KEY,
    setting_value VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default settings
INSERT INTO settings (setting_key, setting_value) VALUES
    ('wait_seconds', '20'),
    ('shortener_mode', 'continuous'),   -- 'continuous' or 'reset'
    ('task_expire_minutes', '30'),
    ('max_active_tasks_per_device', '1'),
    ('free_token_amount', '1'),
    ('free_token_cooldown_hours', '24')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- Sample shorteners (replace URLs with your real shortener destination links later)
INSERT INTO shorteners (seq_order, url, label, enabled) VALUES
    (1, 'https://example-shortener1.com/api?url=', 'Shortener 1', 1),
    (2, 'https://example-shortener2.com/api?url=', 'Shortener 2', 1),
    (3, 'https://example-shortener3.com/api?url=', 'Shortener 3', 1)
ON DUPLICATE KEY UPDATE url = VALUES(url);