-- ============================================================
--  CRYPTO PAYMENT SYSTEM — db_setup.sql
--  Run this once to create all required tables.
--  Command: mysql -u root -p < db_setup.sql
-- ============================================================

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

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)  UNIQUE NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    -- Balances per coin (stored in coin's smallest unit as DECIMAL for precision)
    bal_btc       DECIMAL(18,8) NOT NULL DEFAULT 0.00000000,
    bal_usdt_erc  DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
    bal_usdc_erc  DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
    bal_bnb       DECIMAL(18,8) NOT NULL DEFAULT 0.00000000,
    bal_usdt_bep  DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
    bal_usdt_trc  DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Payment requests (one per payment attempt)
CREATE TABLE IF NOT EXISTS payment_requests (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    payment_id      VARCHAR(64)  UNIQUE NOT NULL,   -- Public-facing unique ID (UUID-style)
    user_id         INT          NOT NULL,
    coin            VARCHAR(20)  NOT NULL,           -- e.g. BTC, USDT_ERC20, BNB ...
    network         VARCHAR(10)  NOT NULL,           -- BTC | ETH | BSC
    pay_address     VARCHAR(100) NOT NULL,           -- Merchant wallet address
    expected_amount DECIMAL(18,8) NOT NULL,          -- Exact amount user must send
    usd_amount      DECIMAL(12,2) NOT NULL DEFAULT 0, -- Approximate USD value
    status          ENUM('pending','detecting','confirmed','expired','failed')
                    NOT NULL DEFAULT 'pending',
    tx_hash         VARCHAR(100) DEFAULT NULL,
    received_amount DECIMAL(18,8) DEFAULT NULL,
    confirmations   INT          NOT NULL DEFAULT 0,
    last_checked_at TIMESTAMP    NULL,
    expires_at      TIMESTAMP    NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_expires (expires_at)
);

-- Confirmed transactions / ledger
CREATE TABLE IF NOT EXISTS transactions (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    user_id             INT          NOT NULL,
    payment_request_id  INT          NOT NULL,
    coin                VARCHAR(20)  NOT NULL,
    amount              DECIMAL(18,8) NOT NULL,
    tx_hash             VARCHAR(100) NOT NULL,
    status              ENUM('pending','confirmed','failed') NOT NULL DEFAULT 'confirmed',
    created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)            REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE
);

-- API sessions / tokens
CREATE TABLE IF NOT EXISTS sessions (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT          NOT NULL,
    token      VARCHAR(255) UNIQUE NOT NULL,
    expires_at TIMESTAMP    NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (token)
);

-- Cron / monitor log (optional, useful for debugging)
CREATE TABLE IF NOT EXISTS monitor_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    payment_id  VARCHAR(64)  NOT NULL,
    coin        VARCHAR(20)  NOT NULL,
    api_checked VARCHAR(50)  NOT NULL,
    result      TEXT,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Withdrawals requests table
CREATE TABLE IF NOT EXISTS withdrawals (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT          NOT NULL,
    coin        VARCHAR(20)  NOT NULL,
    address     VARCHAR(100) NOT NULL,
    amount      DECIMAL(18,8) NOT NULL,
    status      ENUM('pending', 'completed', 'rejected') NOT NULL DEFAULT 'pending',
    tx_hash     VARCHAR(100) DEFAULT NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);