-- ============================================================
-- Crypto Mining Profitability System — Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS crypto_mining
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE crypto_mining;

-- ============================================================
-- Table: miners
-- ============================================================
CREATE TABLE IF NOT EXISTS miners (
    id               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    name             VARCHAR(255)    NOT NULL COMMENT 'Device full name (lowercase)',
    brand            VARCHAR(100)    NOT NULL COMMENT 'Manufacturer (Bitmain, MicroBT, NVIDIA…)',
    type             ENUM('ASIC','GPU') NOT NULL DEFAULT 'ASIC',
    hashrate         DECIMAL(20, 8)  NOT NULL COMMENT 'Normalized to TH/s',
    hashrate_unit    VARCHAR(20)     NOT NULL DEFAULT 'TH/s',
    power_consumption INT UNSIGNED   NOT NULL COMMENT 'Watts',
    efficiency       DECIMAL(12, 4)  NULL     COMMENT 'J/TH — auto-calculated',
    supported_coins  VARCHAR(500)    NULL     COMMENT 'Comma-separated: BTC,LTC,DOGE',
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    -- Prevent duplicates: same device with same hashrate is identical
    UNIQUE KEY uq_miner_name_hashrate (name, hashrate),

    -- Performance indexes
    INDEX idx_miner_type    (type),
    INDEX idx_miner_brand   (brand),
    INDEX idx_miner_hashrate (hashrate DESC),
    FULLTEXT INDEX ft_miner_name (name)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
COMMENT = 'Mining hardware registry — populated by AI seeder';

-- ============================================================
-- Seed: fallback data (15 popular devices)
-- Used when OpenRouter AI is unavailable
-- ============================================================
INSERT IGNORE INTO miners
    (name, brand, type, hashrate, hashrate_unit, power_consumption, efficiency, supported_coins)
VALUES
    ('antminer s21 pro',   'Bitmain', 'ASIC', 234.000000, 'TH/s', 3531, 15.09, 'BTC,BCH,BSV'),
    ('antminer s21',       'Bitmain', 'ASIC', 200.000000, 'TH/s', 3500, 17.50, 'BTC,BCH,BSV'),
    ('whatsminer m60s',    'MicroBT', 'ASIC', 186.000000, 'TH/s', 3441, 18.50, 'BTC,BCH'),
    ('antminer s19 xp hyd','Bitmain', 'ASIC', 257.000000, 'TH/s', 5326, 20.72, 'BTC,BCH,BSV'),
    ('whatsminer m50s++',  'MicroBT', 'ASIC', 148.000000, 'TH/s', 3306, 22.34, 'BTC,BCH'),
    ('antminer s19 pro+',  'Bitmain', 'ASIC', 120.000000, 'TH/s', 3360, 28.00, 'BTC,BCH,BSV'),
    ('avalon made a15',    'Canaan',  'ASIC', 150.000000, 'TH/s', 3400, 22.67, 'BTC,BCH'),
    ('antminer l9',        'Bitmain', 'ASIC', 16.000000,  'TH/s', 3360, 210.00,'LTC,DOGE'),
    ('antminer l7',        'Bitmain', 'ASIC', 9.500000,   'TH/s', 3425, 360.50,'LTC,DOGE'),
    ('goldshell kd-box pro','Goldshell','ASIC',2.600000,  'TH/s',  230, 88.50, 'KDA'),
    ('nvidia rtx 4090',    'NVIDIA',  'GPU',  0.000133,   'TH/s',  350, 2631579.00,'ETH,RVN,ERGO,FLUX'),
    ('nvidia rtx 3090 ti', 'NVIDIA',  'GPU',  0.000120,   'TH/s',  450, 3750000.00,'ETH,RVN,ERGO'),
    ('amd radeon rx 7900 xtx','AMD',  'GPU',  0.000095,   'TH/s',  355, 3736842.00,'ETH,RVN,ERGO'),
    ('nvidia rtx 3080 ti', 'NVIDIA',  'GPU',  0.000118,   'TH/s',  340, 2881355.00,'ETH,RVN,ERGO'),
    ('antminer e9 pro',    'Bitmain', 'ASIC', 3.680000,   'TH/s', 2556, 694.60, 'ETH,ETC');
