-- schema.sql - Base DRY-CONNECT paiement + Hotspot multi-zones (VPN / SSH ready)

CREATE TABLE IF NOT EXISTS routers (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    code            VARCHAR(50) NOT NULL UNIQUE,      -- ex : "zone-bali", "zone-bonamoussadi"
    name            VARCHAR(100) DEFAULT NULL,
    host            VARCHAR(255) NOT NULL,           -- IP ou FQDN pour l'API Mikrotik
                                                     -- exemple :
                                                     --   - IP VPN privée : 192.168.195.132
                                                     --   - ou 127.0.0.1 si tu utilises un tunnel SSH local (ssh -L ...)
    port            INT NOT NULL DEFAULT 8728,       -- 8728 (API) ou 8729 (API TLS)
    api_user        VARCHAR(64) NOT NULL,
    api_password    VARCHAR(128) NOT NULL,
    use_tls         TINYINT(1) NOT NULL DEFAULT 0,
    hotspot_profile VARCHAR(64) NOT NULL DEFAULT 'dry-connect',
    vpn_public_ip   VARCHAR(64) DEFAULT NULL,        -- IP VPN actuelle
    last_seen       DATETIME DEFAULT NULL,           -- dernière mise à jour par le routeur
    api_token       VARCHAR(64) NOT NULL             -- token secret pour mise à jour auto
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    email              VARCHAR(191) DEFAULT NULL,
    phone              VARCHAR(32)  DEFAULT NULL,
    hotspot_username   VARCHAR(64)  DEFAULT NULL,
    hotspot_password   VARCHAR(64)  DEFAULT NULL,
    router_id          INT          DEFAULT NULL,    -- lien vers routers.id
    zone               VARCHAR(50)  DEFAULT NULL,    -- ex : "zone-bali" pour repérer rapidement la zone
    is_active          TINYINT(1)   NOT NULL DEFAULT 0,
    created_at         DATETIME     NOT NULL,
    updated_at         DATETIME     NOT NULL,
    UNIQUE KEY uniq_hotspot_username (hotspot_username),
    KEY idx_users_zone (zone),
    CONSTRAINT fk_users_router FOREIGN KEY (router_id) REFERENCES routers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    user_id            INT          NOT NULL,
    router_id          INT          DEFAULT NULL,    -- routeur concerné par ce paiement
    reference          VARCHAR(100) NOT NULL,
    amount             DECIMAL(10,2) NOT NULL,
    currency           VARCHAR(10)  NOT NULL DEFAULT 'XAF',
    status             VARCHAR(20)  NOT NULL, -- PENDING / SUCCESS / FAILED
    provider           VARCHAR(50)  NOT NULL DEFAULT 'orange_money',
    provider_tx_id     VARCHAR(100) DEFAULT NULL,
    processed          TINYINT(1)   NOT NULL DEFAULT 0,
    created_at         DATETIME     NOT NULL,
    updated_at         DATETIME     NOT NULL,
    CONSTRAINT fk_payments_user   FOREIGN KEY (user_id)  REFERENCES users(id),
    CONSTRAINT fk_payments_router FOREIGN KEY (router_id) REFERENCES routers(id),
    UNIQUE KEY uniq_reference (reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
