-- =====================================================================
-- Painel P2P multi-exchange — schema MySQL 8.0+
-- Charset utf8mb4 em tudo. Valores monetários como DECIMAL (nunca float).
-- Campos sensíveis (secret de API, CPF, telefone, PIX) são guardados
-- CRIPTOGRAFADOS pela aplicação (AES-256-GCM) — o banco só vê o ciphertext.
-- =====================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ---------------------------------------------------------------------
-- Usuários do painel (você + funcionários) e papéis
-- ---------------------------------------------------------------------
CREATE TABLE panel_users (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(120)    NOT NULL,
  email         VARCHAR(190)    NOT NULL,
  password_hash VARCHAR(255)    NOT NULL,          -- bcrypt/argon2
  role          ENUM('admin','operator') NOT NULL DEFAULT 'operator',
  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,
  PRIMARY KEY (id),
  UNIQUE KEY uq_panel_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Contas de exchange (Binance, Bybit, ...). Suporta multi-conta.
-- A secret_key NUNCA é guardada em texto puro: api_secret_enc guarda o
-- ciphertext + nonce/tag (formato definido pela camada de cripto da app).
-- Proxy é por conta (cada exchange/conta pode ter o seu).
-- ---------------------------------------------------------------------
CREATE TABLE exchange_accounts (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  label           VARCHAR(120)    NOT NULL,        -- nome amigável no painel
  exchange        ENUM('binance','bybit') NOT NULL,
  api_key         VARCHAR(255)    NOT NULL,        -- pode ficar em claro (não é o segredo)
  api_secret_enc  VARBINARY(1024) NOT NULL,        -- AES-256-GCM (nonce|tag|ciphertext)
  binance_user_id VARCHAR(64)     NULL,            -- x-user-id (Binance C2C)
  -- Proxy desta conta
  proxy_enabled   TINYINT(1)      NOT NULL DEFAULT 0,
  proxy_protocol  ENUM('http','https','socks5') NULL,
  proxy_host      VARCHAR(190)    NULL,
  proxy_port      SMALLINT UNSIGNED NULL,
  proxy_user      VARCHAR(190)    NULL,
  proxy_pass_enc  VARBINARY(512)  NULL,            -- senha do proxy, também criptografada
  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,
  PRIMARY KEY (id),
  KEY idx_exchange_accounts_exchange (exchange, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Anúncios espelhados localmente. payload_raw guarda o JSON cru da
-- exchange para campos que ainda não promovemos a colunas.
-- ---------------------------------------------------------------------
CREATE TABLE ads (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id        BIGINT UNSIGNED NOT NULL,
  adv_no            VARCHAR(64)     NOT NULL,       -- id do anúncio na exchange
  asset             VARCHAR(20)     NOT NULL,       -- USDT, BTC...
  fiat              VARCHAR(10)     NOT NULL,       -- BRL...
  trade_type        ENUM('BUY','SELL') NOT NULL,    -- ponto de vista do anunciante
  price             DECIMAL(24,8)   NULL,
  price_type        TINYINT         NULL,           -- fixo/flutuante (confirmar valores)
  status            INT             NULL,           -- status numérico da exchange
  surplus_amount    DECIMAL(24,8)   NULL,
  min_amount        DECIMAL(24,8)   NULL,
  max_amount        DECIMAL(24,8)   NULL,
  payload_raw       JSON            NULL,
  synced_at         TIMESTAMP       NULL,
  created_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ads_account_advno (account_id, adv_no),
  KEY idx_ads_pair (asset, fiat, trade_type),
  CONSTRAINT fk_ads_account FOREIGN KEY (account_id) REFERENCES exchange_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Configuração de auto-pricing POR anúncio (só os que você marcar).
-- As três travas: seguir concorrente -> piso de lucro -> piso de spot.
-- ---------------------------------------------------------------------
CREATE TABLE ad_pricing_config (
  ad_id              BIGINT UNSIGNED NOT NULL,
  enabled            TINYINT(1)      NOT NULL DEFAULT 0,
  -- Estratégia de seguir concorrente
  follow_strategy    ENUM('beat_top','match_rank') NOT NULL DEFAULT 'beat_top',
  beat_amount        DECIMAL(24,8)   NULL,          -- p/ beat_top: quanto melhor que o topo
  target_rank        INT             NULL,          -- p/ match_rank: casar com o N-ésimo
  competitor_filter  JSON            NULL,          -- métodos de pgto, KYC etc. p/ filtrar concorrentes
  -- Travas
  min_profit_price   DECIMAL(24,8)   NULL,          -- piso de lucro (preço mínimo absoluto)
  spot_guard_enabled TINYINT(1)      NOT NULL DEFAULT 1,
  spot_symbol        VARCHAR(20)     NULL,          -- ex: USDTBRL (par spot da Binance)
  spot_margin_ratio  DECIMAL(10,6)   NULL,          -- margem sobre o spot (ex: 1.005 = +0,5%)
  -- Controle de frequência
  min_price_step     DECIMAL(24,8)   NULL,          -- só atualiza se mudar mais que isto
  last_run_at        TIMESTAMP       NULL,
  updated_at         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (ad_id),
  CONSTRAINT fk_pricing_ad FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Histórico de ajustes de preço (auditoria do worker de pricing)
CREATE TABLE price_adjustments (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ad_id           BIGINT UNSIGNED NOT NULL,
  old_price       DECIMAL(24,8)   NULL,
  new_price       DECIMAL(24,8)   NULL,
  competitor_price DECIMAL(24,8)  NULL,
  spot_price      DECIMAL(24,8)   NULL,
  reason          VARCHAR(255)    NULL,             -- qual trava decidiu o preço final
  applied         TINYINT(1)      NOT NULL DEFAULT 0,
  created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_price_adj_ad (ad_id, created_at),
  CONSTRAINT fk_price_adj_ad FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Contrapartes (clientes). Guarda se já validou CPF/telefone comigo.
-- Dados pessoais CRIPTOGRAFADOS (LGPD). counterparty_ref é o
-- identificador do cliente na exchange.
-- ---------------------------------------------------------------------
CREATE TABLE counterparties (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  exchange           ENUM('binance','bybit') NOT NULL,
  counterparty_ref   VARCHAR(128)    NOT NULL,      -- id/nick do cliente na exchange
  display_name       VARCHAR(190)    NULL,
  cpf_enc            VARBINARY(512)  NULL,          -- criptografado
  phone_enc          VARBINARY(512)  NULL,          -- criptografado
  pix_key_enc        VARBINARY(512)  NULL,          -- criptografado
  validated          TINYINT(1)      NOT NULL DEFAULT 0,
  validated_at       TIMESTAMP       NULL,
  first_order_at     TIMESTAMP       NULL,
  total_orders       INT             NOT NULL DEFAULT 0,
  notes              TEXT            NULL,
  created_at         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_counterparty (exchange, counterparty_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Ordens espelhadas localmente.
-- ---------------------------------------------------------------------
CREATE TABLE orders (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id        BIGINT UNSIGNED NOT NULL,
  order_no          VARCHAR(64)     NOT NULL,
  adv_no            VARCHAR(64)     NULL,
  counterparty_id   BIGINT UNSIGNED NULL,
  trade_type        ENUM('BUY','SELL') NOT NULL,    -- ponto de vista do anunciante
  asset             VARCHAR(20)     NOT NULL,
  fiat              VARCHAR(10)     NOT NULL,
  amount            DECIMAL(24,8)   NULL,
  total_price       DECIMAL(24,8)   NULL,
  order_status      INT             NULL,
  status_text       VARCHAR(60)     NULL,
  payload_raw       JSON            NULL,
  -- flags de automação já disparada (evita reenvio)
  asked_validation  TINYINT(1)      NOT NULL DEFAULT 0,
  asked_pix         TINYINT(1)      NOT NULL DEFAULT 0,
  order_created_at  TIMESTAMP       NULL,
  synced_at         TIMESTAMP       NULL,
  created_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_orders_account_orderno (account_id, order_no),
  KEY idx_orders_status (order_status),
  KEY idx_orders_counterparty (counterparty_id),
  CONSTRAINT fk_orders_account FOREIGN KEY (account_id) REFERENCES exchange_accounts(id),
  CONSTRAINT fk_orders_counterparty FOREIGN KEY (counterparty_id) REFERENCES counterparties(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Templates de mensagem por gatilho (editáveis no painel).
-- body suporta variáveis: {nome} {valor} {ordem} {asset} {fiat} ...
-- ---------------------------------------------------------------------
CREATE TABLE message_templates (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  trigger_key ENUM('paid_new_customer_validation','counterparty_selling_pix','manual') NOT NULL,
  title       VARCHAR(120)    NOT NULL,
  body        TEXT            NOT NULL,
  enabled     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,
  PRIMARY KEY (id),
  KEY idx_templates_trigger (trigger_key, enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Mensagens de chat espelhadas + ponte com o Telegram.
-- ---------------------------------------------------------------------
CREATE TABLE chat_messages (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id          BIGINT UNSIGNED NULL,
  order_no          VARCHAR(64)     NOT NULL,
  exchange_msg_id   VARCHAR(64)     NULL,           -- id da msg na exchange (dedupe)
  direction         ENUM('in','out') NOT NULL,      -- in = do cliente, out = nossa
  msg_type          VARCHAR(20)     NOT NULL DEFAULT 'text',
  content           TEXT            NULL,
  sent_by_automation TINYINT(1)     NOT NULL DEFAULT 0,
  -- Ponte Telegram
  telegram_message_id BIGINT        NULL,           -- id da msg que o bot postou no grupo
  telegram_replied    TINYINT(1)    NOT NULL DEFAULT 0,
  created_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_chat_exchange_msg (order_no, exchange_msg_id),
  KEY idx_chat_order (order_id),
  KEY idx_chat_tg (telegram_message_id),
  CONSTRAINT fk_chat_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Cache de preço spot (par -> último preço), alimentado periodicamente.
-- ---------------------------------------------------------------------
CREATE TABLE spot_prices (
  symbol      VARCHAR(20)   NOT NULL,               -- USDTBRL, BTCBRL...
  price       DECIMAL(24,8) NOT NULL,
  fetched_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (symbol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Configuração geral (chave/valor) — token do bot Telegram (criptografado),
-- chat_id do grupo, etc.
-- ---------------------------------------------------------------------
CREATE TABLE app_settings (
  setting_key   VARCHAR(120)   NOT NULL,
  value_text    TEXT           NULL,
  value_enc     VARBINARY(2048) NULL,               -- p/ segredos (token do bot)
  updated_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Log de auditoria (quem fez o quê no painel) — útil com funcionários.
-- ---------------------------------------------------------------------
CREATE TABLE audit_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NULL,
  action      VARCHAR(120)    NOT NULL,
  entity      VARCHAR(60)     NULL,
  entity_id   VARCHAR(64)     NULL,
  detail      JSON            NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_user (user_id, created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES panel_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;