-- =====================================================================
-- TradeHub - schema MySQL (cPanel / MySQL 5.7+ ou MariaDB 10.3+)
-- Todas as datas sao guardadas em UTC.
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- Contas MT5
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contas (
  login            BIGINT UNSIGNED NOT NULL,
  alias            VARCHAR(120)    NULL,          -- "Blue Guardian 1"
  grupo            VARCHAR(60)     NULL,          -- "prop", "pessoal", ...
  servidor         VARCHAR(120)    NULL,
  corretora        VARCHAR(120)    NULL,
  moeda            VARCHAR(8)      NULL,
  alavancagem      INT             NULL,
  saldo            DECIMAL(18,2)   NULL,
  equity           DECIMAL(18,2)   NULL,
  margem_livre     DECIMAL(18,2)   NULL,
  saldo_inicial    DECIMAL(18,2)   NULL,          -- base para normalizacao em %
  host             VARCHAR(120)    NULL,          -- maquina/VPS que reportou
  offset_servidor  TINYINT         NULL,          -- horas vs UTC detetadas
  ativa            TINYINT(1)      NOT NULL DEFAULT 1,
  visto_em         DATETIME        NULL,
  criada_em        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Trades fechadas (uma linha por posicao reconstruida)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS trades (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  login         BIGINT UNSIGNED NOT NULL,
  position_id   BIGINT UNSIGNED NOT NULL,
  simbolo       VARCHAR(32)     NOT NULL,
  tipo          ENUM('buy','sell') NOT NULL,
  volume        DECIMAL(12,4)   NOT NULL,
  abertura      DATETIME        NOT NULL,
  fecho         DATETIME        NOT NULL,
  preco_abrir   DECIMAL(20,8)   NOT NULL,
  preco_fechar  DECIMAL(20,8)   NOT NULL,
  sl            DECIMAL(20,8)   NULL,
  tp            DECIMAL(20,8)   NULL,
  lucro         DECIMAL(18,2)   NOT NULL DEFAULT 0,
  comissao      DECIMAL(18,2)   NOT NULL DEFAULT 0,
  swap          DECIMAL(18,2)   NOT NULL DEFAULT 0,
  taxa          DECIMAL(18,2)   NOT NULL DEFAULT 0,
  liquido       DECIMAL(18,2)   NOT NULL DEFAULT 0,   -- lucro+comissao+swap+taxa
  magic         BIGINT          NOT NULL DEFAULT 0,
  comentario    VARCHAR(64)     NULL,
  ea            VARCHAR(32)     NULL,               -- prefixo do comentario (ex: FR)
  duracao_s     INT UNSIGNED    NOT NULL DEFAULT 0,
  atualizada_em DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_trade (login, position_id),
  KEY ix_login_fecho (login, fecho),
  KEY ix_fecho (fecho),
  KEY ix_simbolo (simbolo),
  KEY ix_magic (magic),
  KEY ix_ea (ea),
  CONSTRAINT fk_trades_conta FOREIGN KEY (login)
    REFERENCES contas (login) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Posicoes abertas (snapshot substituido a cada sincronizacao)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS posicoes (
  login         BIGINT UNSIGNED NOT NULL,
  ticket        BIGINT UNSIGNED NOT NULL,
  simbolo       VARCHAR(32)     NOT NULL,
  tipo          ENUM('buy','sell') NOT NULL,
  volume        DECIMAL(12,4)   NOT NULL,
  abertura      DATETIME        NOT NULL,
  preco_abrir   DECIMAL(20,8)   NOT NULL,
  preco_atual   DECIMAL(20,8)   NULL,
  sl            DECIMAL(20,8)   NULL,
  tp            DECIMAL(20,8)   NULL,
  lucro         DECIMAL(18,2)   NOT NULL DEFAULT 0,
  swap          DECIMAL(18,2)   NOT NULL DEFAULT 0,
  magic         BIGINT          NOT NULL DEFAULT 0,
  comentario    VARCHAR(64)     NULL,
  ea            VARCHAR(32)     NULL,
  atualizada_em DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (login, ticket),
  KEY ix_pos_login (login),
  CONSTRAINT fk_pos_conta FOREIGN KEY (login)
    REFERENCES contas (login) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Snapshots de saldo/equity (curva real, inclui flutuante)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS equity_hist (
  id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  login    BIGINT UNSIGNED NOT NULL,
  ts       DATETIME        NOT NULL,
  saldo    DECIMAL(18,2)   NOT NULL,
  equity   DECIMAL(18,2)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_eq (login, ts),
  KEY ix_eq_login_ts (login, ts),
  CONSTRAINT fk_eq_conta FOREIGN KEY (login)
    REFERENCES contas (login) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Registo de sincronizacoes (diagnostico dos agentes)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sincronizacoes (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  login        BIGINT UNSIGNED NULL,
  host         VARCHAR(120)    NULL,
  ts           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  trades_novas INT             NOT NULL DEFAULT 0,
  trades_upd   INT             NOT NULL DEFAULT 0,
  posicoes     INT             NOT NULL DEFAULT 0,
  erro         VARCHAR(255)    NULL,
  PRIMARY KEY (id),
  KEY ix_sync_ts (ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;