-- =====================================================================
-- CarScrets — schema enxuto do checkout
-- Um produto digital (curso online), com N planos.
-- =====================================================================

SET NAMES utf8mb4;

-- ── PLANOS ────────────────────────────────────────────────────────────
-- Uma linha por ticket. Para criar um plano novo, basta inserir aqui.
CREATE TABLE IF NOT EXISTS plans (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(32)  NOT NULL UNIQUE,        -- o botão da landing manda isto
  label       VARCHAR(120) NOT NULL,               -- tag do plano (Starter/Complete)
  offer_name  VARCHAR(160) NULL,                    -- nome de exibição da oferta
  price_cents INT UNSIGNED NOT NULL,               -- em centavos: 1990, 2590
  currency    CHAR(3)      NOT NULL DEFAULT 'usd',
  image_url   VARCHAR(255) NULL,
  timer_enabled TINYINT(1)  NULL,                     -- NULL = usa global
  timer_minutes INT UNSIGNED NULL,
  active      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO plans (code, label, price_cents, currency, image_url) VALUES
  ('plan001', 'Starter',  1990, 'usd', '/assets/uploads/product-carsecrets.svg'),
  ('plan002', 'Complete', 2590, 'usd', '/assets/uploads/product-carsecrets.svg')
ON DUPLICATE KEY UPDATE label=VALUES(label), price_cents=VALUES(price_cents);

-- ── CLIENTES ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS customers (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(190) NOT NULL,
  name        VARCHAR(160) NOT NULL,
  phone       VARCHAR(40)  NULL,
  addr_line1   VARCHAR(200) NULL,
  number       VARCHAR(40)  NULL,
  neighborhood VARCHAR(120) NULL,
  addr_line2   VARCHAR(200) NULL,
  city        VARCHAR(120) NULL,
  state       VARCHAR(120) NULL,
  postal_code VARCHAR(40)  NULL,
  country     CHAR(2)      NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── PEDIDOS (base do dashboard) ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS orders (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id  INT UNSIGNED NULL,
  plan_code    VARCHAR(32)  NOT NULL,
  intent_id    VARCHAR(64)  NOT NULL UNIQUE,         -- idempotência do webhook
  amount_cents INT UNSIGNED NOT NULL,                -- bruto cobrado
  fee_cents    INT UNSIGNED NULL,                    -- taxa Stripe (balance_transaction)
  net_cents    INT UNSIGNED NULL,                    -- líquido (balance_transaction)
  currency     CHAR(3)      NOT NULL DEFAULT 'usd',
  status       ENUM('pending','paid','refunded','disputed','failed') NOT NULL DEFAULT 'pending',
  track_json   JSON         NULL,                    -- UTMs reais p/ UTMify
  event_id     VARCHAR(64)  NULL,                    -- dedup pixel <-> CAPI
  capi_sent    TINYINT(1)   NOT NULL DEFAULT 0,
  utmify_sent  TINYINT(1)   NOT NULL DEFAULT 0,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at      DATETIME     NULL,
  INDEX idx_status_paid (status, paid_at),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SETTINGS (chave/valor: produto + pixels) ─────────────────────────
CREATE TABLE IF NOT EXISTS settings (
  k VARCHAR(64) PRIMARY KEY,
  v TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (k, v) VALUES
  ('product_name',            'CarScrets'),
  ('product_author',          'Walter'),
  ('product_category',        'DigitalCourse'),
  ('company_logo_url',        '/assets/uploads/logo-carsecrets.svg'),
  ('default_lang',            'en'),
  ('timer_enabled',           '1'),
  ('timer_minutes',           '15'),
  ('timer_headline_pt',       'Oferta por tempo limitado'),
  ('timer_headline_en',       'Limited-time offer'),
  ('timer_headline_es',       'Oferta por tiempo limitado'),
  ('button_color',            '#36453d'),
  ('company_name',            '')
ON DUPLICATE KEY UPDATE v = v;


-- ── PIXELS (multipixel: vários Meta e Google) ────────────────────────
CREATE TABLE IF NOT EXISTS pixels (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type       ENUM('meta','google') NOT NULL,
  name       VARCHAR(120) NULL,
  pixel_id   VARCHAR(64)  NOT NULL,
  token      VARCHAR(512) NULL,
  label      VARCHAR(120) NULL,
  test_code  VARCHAR(64)  NULL,
  active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_type_active (type, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ADMIN (login simples) ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS admin_users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(60) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Crie o admin com:  php -r "echo password_hash('SUA_SENHA', PASSWORD_DEFAULT);"
-- depois:  INSERT INTO admin_users (username, password_hash) VALUES ('admin', '<hash>');