-- =====================================================
-- INSTALAÇÃO DO SISTEMA COMPLETO: Wallet + Depósito +
-- Saque + Investimento + MLM + Leilão
-- Execute este arquivo no seu banco de dados MySQL
-- =====================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ════════════════════════════════════════════════════
-- MÓDULO 1: CARTEIRA (WALLET)
-- ════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `wallet` (
  `id`                   INT          NOT NULL AUTO_INCREMENT,
  `customer_id`          INT          NOT NULL,
  `balance_available`    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `balance_pending`      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `balance_total_earned` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `updated_at`           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `wallet_transactions` (
  `id`              INT NOT NULL AUTO_INCREMENT,
  `customer_id`     INT NOT NULL,
  `type`            ENUM(
                      'deposit','yield','binary_bonus','unilevel_bonus',
                      'auction_prize','raffle_prize','bid_cost',
                      'investment','withdrawal','upgrade','adjustment'
                    ) NOT NULL,
  `amount`          DECIMAL(10,2) NOT NULL,
  `balance_before`  DECIMAL(10,2) DEFAULT NULL,
  `balance_after`   DECIMAL(10,2) DEFAULT NULL,
  `description`     VARCHAR(500)  DEFAULT NULL,
  `reference_id`    INT           DEFAULT NULL,
  `reference_type`  VARCHAR(50)   DEFAULT NULL,
  `created_at`      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_type`     (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ════════════════════════════════════════════════════
-- MÓDULO 2: DEPÓSITOS
-- ════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `deposits` (
  `id`               INT          NOT NULL AUTO_INCREMENT,
  `customer_id`      INT          NOT NULL,
  `amount`           DECIMAL(10,2) NOT NULL,
  `status`           TINYINT      NOT NULL DEFAULT 1 COMMENT '1=pendente 2=pago 3=cancelado',
  `payment_method`   VARCHAR(100) DEFAULT NULL,
  `pix_code`         TEXT         DEFAULT NULL,
  `pix_qrcode`       TEXT         DEFAULT NULL,
  `id_mp`            VARCHAR(255) DEFAULT NULL,
  `txid`             VARCHAR(255) DEFAULT NULL,
  `order_expiration` INT          NOT NULL DEFAULT 30,
  `confirmed_at`     DATETIME     DEFAULT NULL,
  `created_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status`   (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ════════════════════════════════════════════════════
-- MÓDULO 3: SAQUES
-- ════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id`           INT          NOT NULL AUTO_INCREMENT,
  `customer_id`  INT          NOT NULL,
  `amount`       DECIMAL(10,2) NOT NULL,
  `fee`          DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `amount_net`   DECIMAL(10,2) GENERATED ALWAYS AS (`amount` - `fee`) STORED,
  `status`       TINYINT      NOT NULL DEFAULT 1 COMMENT '1=pendente 2=aprovado 3=recusado',
  `pix_key`      VARCHAR(255) DEFAULT NULL,
  `pix_key_type` VARCHAR(50)  DEFAULT NULL COMMENT 'cpf|telefone|email|aleatorio',
  `admin_note`   TEXT         DEFAULT NULL,
  `approved_at`  DATETIME     DEFAULT NULL,
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status`   (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ════════════════════════════════════════════════════
-- MÓDULO 4: INVESTIMENTOS
-- ════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `investment_plans` (
  `id`              INT          NOT NULL AUTO_INCREMENT,
  `name`            VARCHAR(100) NOT NULL,
  `min_amount`      DECIMAL(10,2) NOT NULL,
  `max_amount`      DECIMAL(10,2) NOT NULL,
  `daily_rate`      DECIMAL(5,2) NOT NULL,
  `cap_multiplier`  DECIMAL(5,2) NOT NULL DEFAULT 2.00 COMMENT '2.00 = 200% do investido',
  `duration_days`   INT          DEFAULT NULL COMMENT 'NULL = sem limite de dias',
  `color`           VARCHAR(20)  NOT NULL DEFAULT '#6c5ce7',
  `icon`            VARCHAR(10)  NOT NULL DEFAULT '💎',
  `sort_order`      INT          NOT NULL DEFAULT 0,
  `is_active`       TINYINT      NOT NULL DEFAULT 1,
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `investment_plans` (`name`,`min_amount`,`max_amount`,`daily_rate`,`cap_multiplier`,`color`,`icon`,`sort_order`) VALUES
('Plano Bronze',  50.00,  499.00, 1.50, 2.00, '#cd7f32', '🥉', 1),
('Plano Prata',  500.00, 1499.00, 2.00, 2.00, '#c0c0c0', '🥈', 2),
('Plano Ouro',  1500.00, 4999.00, 2.50, 2.00, '#ffd700', '🥇', 3),
('Plano Diamante', 5000.00, 99999.00, 3.00, 2.00, '#00cec9', '💎', 4);

CREATE TABLE IF NOT EXISTS `investments` (
  `id`              INT          NOT NULL AUTO_INCREMENT,
  `customer_id`     INT          NOT NULL,
  `plan_id`         INT          NOT NULL,
  `amount_invested` DECIMAL(10,2) NOT NULL,
  `amount_earned`   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `cap_limit`       DECIMAL(10,2) NOT NULL,
  `daily_rate`      DECIMAL(5,2) NOT NULL,
  `status`          TINYINT      NOT NULL DEFAULT 1 COMMENT '1=ativo 2=encerrado 3=cancelado',
  `last_yield_at`   DATETIME     DEFAULT NULL,
  `ended_at`        DATETIME     DEFAULT NULL,
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status`   (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `investment_yields` (
  `id`            INT          NOT NULL AUTO_INCREMENT,
  `investment_id` INT          NOT NULL,
  `customer_id`   INT          NOT NULL,
  `amount`        DECIMAL(10,2) NOT NULL,
  `day_number`    INT          NOT NULL DEFAULT 1,
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_investment` (`investment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ════════════════════════════════════════════════════
-- MÓDULO 5: REDE BINÁRIA + UNILEVEL
-- ════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `binary_tree` (
  `id`                  INT    NOT NULL AUTO_INCREMENT,
  `customer_id`         INT    NOT NULL,
  `sponsor_id`          INT    DEFAULT NULL,
  `parent_id`           INT    DEFAULT NULL,
  `side`                ENUM('left','right') DEFAULT NULL,
  `left_id`             INT    DEFAULT NULL,
  `right_id`            INT    DEFAULT NULL,
  `left_volume`         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `right_volume`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `left_carry`          DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `right_carry`         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_binary_earned` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `binary_bonuses` (
  `id`                INT          NOT NULL AUTO_INCREMENT,
  `customer_id`       INT          NOT NULL,
  `weak_side_volume`  DECIMAL(10,2) NOT NULL,
  `bonus_rate`        DECIMAL(5,2) NOT NULL,
  `amount`            DECIMAL(10,2) NOT NULL,
  `left_volume_used`  DECIMAL(10,2) NOT NULL,
  `right_volume_used` DECIMAL(10,2) NOT NULL,
  `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `unilevel_rates` (
  `level` TINYINT NOT NULL,
  `rate`  DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `unilevel_rates` VALUES
(1,10.00),(2,5.00),(3,3.00),(4,2.00),(5,1.00),
(6,1.00),(7,0.50),(8,0.50),(9,0.50),(10,0.50);

CREATE TABLE IF NOT EXISTS `unilevel_commissions` (
  `id`                  INT          NOT NULL AUTO_INCREMENT,
  `beneficiary_id`      INT          NOT NULL,
  `origin_customer_id`  INT          NOT NULL,
  `investment_id`       INT          DEFAULT NULL,
  `level`               TINYINT      NOT NULL,
  `rate`                DECIMAL(5,2) NOT NULL,
  `base_amount`         DECIMAL(10,2) NOT NULL,
  `commission_amount`   DECIMAL(10,2) NOT NULL,
  `created_at`          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_beneficiary`   (`beneficiary_id`),
  KEY `idx_origin`        (`origin_customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ════════════════════════════════════════════════════
-- MÓDULO 6: LEILÃO DE CENTAVOS
-- ════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `auction_list` (
  `id`               INT          NOT NULL AUTO_INCREMENT,
  `name`             VARCHAR(255) NOT NULL,
  `description`      TEXT         DEFAULT NULL,
  `image_path`       VARCHAR(500) DEFAULT NULL,
  `slug`             VARCHAR(255) DEFAULT NULL,
  `real_value`       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `start_price`      DECIMAL(10,2) NOT NULL DEFAULT 0.01,
  `current_price`    DECIMAL(10,2) NOT NULL DEFAULT 0.01,
  `bid_increment`    DECIMAL(10,2) NOT NULL DEFAULT 0.01,
  `bid_credit_cost`  INT          NOT NULL DEFAULT 1,
  `timer_seconds`    INT          NOT NULL DEFAULT 30,
  `timer_reset_at`   DATETIME     DEFAULT NULL,
  `phase`            TINYINT      NOT NULL DEFAULT 1 COMMENT '1=fake nunca zera 2=real zera',
  `phase2_trigger`   DECIMAL(10,2) DEFAULT NULL COMMENT 'Receita pra virar fase real',
  `total_bids`       INT          NOT NULL DEFAULT 0,
  `total_revenue`    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `last_bidder_id`   INT          DEFAULT NULL,
  `winner_id`        INT          DEFAULT NULL,
  `status`           TINYINT      NOT NULL DEFAULT 1 COMMENT '1=em breve 2=ativo 3=encerrado',
  `featured`         TINYINT      NOT NULL DEFAULT 0,
  `created_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `auction_bids` (
  `id`           INT     NOT NULL AUTO_INCREMENT,
  `auction_id`   INT     NOT NULL,
  `customer_id`  INT     DEFAULT NULL,
  `price_at_bid` DECIMAL(10,2) NOT NULL,
  `is_simulated` TINYINT NOT NULL DEFAULT 0,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_auction`  (`auction_id`),
  KEY `idx_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `bid_credits` (
  `id`          INT  NOT NULL AUTO_INCREMENT,
  `customer_id` INT  NOT NULL,
  `credits`     INT  NOT NULL DEFAULT 0,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `bid_packages` (
  `id`          INT          NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `credits`     INT          NOT NULL,
  `price`       DECIMAL(10,2) NOT NULL,
  `is_featured` TINYINT      NOT NULL DEFAULT 0,
  `is_active`   TINYINT      NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `bid_packages` (`name`,`credits`,`price`,`is_featured`) VALUES
('Starter',  10,   4.90, 0),
('Popular',  30,  12.90, 1),
('Pro',     100,  39.90, 0),
('Ultra',   300,  99.90, 0);

CREATE TABLE IF NOT EXISTS `bid_package_orders` (
  `id`             INT          NOT NULL AUTO_INCREMENT,
  `customer_id`    INT          NOT NULL,
  `package_id`     INT          NOT NULL,
  `credits_qty`    INT          NOT NULL,
  `amount`         DECIMAL(10,2) NOT NULL,
  `status`         TINYINT      NOT NULL DEFAULT 1,
  `payment_method` VARCHAR(100) DEFAULT NULL,
  `pix_code`       TEXT         DEFAULT NULL,
  `pix_qrcode`     TEXT         DEFAULT NULL,
  `id_mp`          VARCHAR(255) DEFAULT NULL,
  `txid`           VARCHAR(255) DEFAULT NULL,
  `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ════════════════════════════════════════════════════
-- MÓDULO 7: PROVA SOCIAL + NOTIFICAÇÕES WHATSAPP
-- ════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `social_proof_names` (
  `id`   INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `social_proof_names` (`name`,`city`) VALUES
('Carlos S.','São Paulo'),('Ana L.','Rio de Janeiro'),
('Marcos F.','Belo Horizonte'),('Juliana R.','Curitiba'),
('Pedro M.','Salvador'),('Fernanda C.','Fortaleza'),
('Lucas O.','Manaus'),('Camila T.','Recife'),
('Rafael B.','Porto Alegre'),('Beatriz N.','Goiânia'),
('Thiago A.','Brasília'),('Larissa M.','Florianópolis'),
('Diego V.','Belém'),('Isabela C.','Campo Grande'),
('Rodrigo L.','Natal'),('Priscila S.','Teresina'),
('Gustavo P.','São Luís'),('Vanessa F.','Maceió'),
('Henrique D.','João Pessoa'),('Aline K.','Macapá');

CREATE TABLE IF NOT EXISTS `notification_queue` (
  `id`            INT          NOT NULL AUTO_INCREMENT,
  `customer_id`   INT          NOT NULL,
  `phone`         VARCHAR(20)  DEFAULT NULL,
  `event_type`    VARCHAR(50)  NOT NULL,
  `message`       TEXT         NOT NULL,
  `status`        TINYINT      NOT NULL DEFAULT 0 COMMENT '0=pendente 1=enviado 2=erro',
  `scheduled_for` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sent_at`       DATETIME     DEFAULT NULL,
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ════════════════════════════════════════════════════
-- CONFIGURAÇÕES DO SISTEMA (novas chaves)
-- ════════════════════════════════════════════════════

INSERT IGNORE INTO `system_info` (`meta_field`, `meta_value`) VALUES
('withdrawal_min_amount', '20.00'),
('withdrawal_fee_percent', '5.00'),
('deposit_min_amount', '10.00'),
('deposit_expiration_minutes', '30'),
('binary_bonus_rate', '10.00'),
('binary_flush_mode', 'weekly'),
('investment_active', '1'),
('auction_active', '1'),
('platform_currency', 'BRL'),
('wpp_api_url', ''),
('wpp_api_key', '');

SET FOREIGN_KEY_CHECKS = 1;

-- ════════════════════════════════════════════════════
-- TRIGGER: Criar wallet automaticamente para novos clientes
-- ════════════════════════════════════════════════════
DROP TRIGGER IF EXISTS `after_customer_insert`;
DELIMITER $$
CREATE TRIGGER `after_customer_insert`
AFTER INSERT ON `customer_list`
FOR EACH ROW
BEGIN
  INSERT IGNORE INTO `wallet` (`customer_id`) VALUES (NEW.id);
  INSERT IGNORE INTO `bid_credits` (`customer_id`, `credits`) VALUES (NEW.id, 0);
  INSERT IGNORE INTO `binary_tree` (`customer_id`) VALUES (NEW.id);
END$$
DELIMITER ;
