-- ============================================
-- Token Manpower Panel - Database Schema
-- ============================================

CREATE DATABASE IF NOT EXISTS token_manpower CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE token_manpower;

CREATE TABLE users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50)  NOT NULL UNIQUE,
    password        VARCHAR(255) NOT NULL,
    full_name       VARCHAR(100) NOT NULL,
    role            ENUM('admin','user') NOT NULL DEFAULT 'user',
    hourly_rate     DECIMAL(10,2) NOT NULL DEFAULT 0.00,   -- tokens per script per hour
    scripts_running INT NOT NULL DEFAULT 7,                 -- usually 7-14
    token_balance   DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE token_generation_log (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    user_id          INT NOT NULL,
    tokens_generated DECIMAL(10,2) NOT NULL,
    scripts_running  INT NOT NULL,
    hourly_rate      DECIMAL(10,2) NOT NULL,
    generated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE transfers (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    from_user_id  INT NOT NULL,
    to_user_id    INT NOT NULL,
    amount        DECIMAL(10,2) NOT NULL,
    status        TINYINT NOT NULL DEFAULT 1,  -- 1=Pending, 2=Success, 3=Failed
    admin_note    VARCHAR(255) DEFAULT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (from_user_id) REFERENCES users(id),
    FOREIGN KEY (to_user_id) REFERENCES users(id)
);

CREATE INDEX idx_transfers_from ON transfers(from_user_id);
CREATE INDEX idx_transfers_to ON transfers(to_user_id);
CREATE INDEX idx_gen_user ON token_generation_log(user_id);
