-- ============================================================================
-- GreenMart — esquema MySQL / MariaDB (cPanel)
-- Versão 1.0 | utf8mb4 | InnoDB
-- Regra: valores monetários SEMPRE em centavos (INT UNSIGNED).
-- Importar no phpMyAdmin, no banco criado em "MySQL® Databases".
-- ============================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  `key`      VARCHAR(80)  NOT NULL PRIMARY KEY,
  `value`    VARCHAR(500) NOT NULL,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- usuários --
CREATE TABLE IF NOT EXISTS users (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120) NOT NULL,
  email         VARCHAR(190) NOT NULL UNIQUE,
  phone         VARCHAR(20)  NULL,
  cpf           CHAR(11)     NULL,            -- necessário para cobrança PIX; só dígitos
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('customer','admin','operator') NOT NULL DEFAULT 'customer',
  status        ENUM('active','blocked')            NOT NULL DEFAULT 'active',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_tokens (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      BIGINT UNSIGNED NOT NULL,
  token_hash   CHAR(64)     NOT NULL UNIQUE,  -- sha256 do token; o token em si não é guardado
  device_name  VARCHAR(100) NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at   DATETIME NOT NULL,
  KEY idx_tokens_user (user_id),
  KEY idx_tokens_exp (expires_at),
  CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS addresses (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  label       VARCHAR(40)  NOT NULL,          -- "Casa", "Trabalho"
  recipient   VARCHAR(120) NOT NULL,
  zip         CHAR(8)      NOT NULL,
  street      VARCHAR(160) NOT NULL,
  number      VARCHAR(20)  NOT NULL,
  complement  VARCHAR(120) NULL,
  district    VARCHAR(100) NOT NULL,
  city        VARCHAR(100) NOT NULL,
  state       CHAR(2)      NOT NULL,
  latitude    DECIMAL(10,7) NULL,
  longitude   DECIMAL(10,7) NULL,
  is_default  TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_addr_user (user_id),
  CONSTRAINT fk_addr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------- catálogo --
-- Árvore: categoria > subcategoria > subcategoria filha (como no app).
CREATE TABLE IF NOT EXISTS categories (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id   INT UNSIGNED NULL,
  name        VARCHAR(120) NOT NULL,
  slug        VARCHAR(140) NOT NULL UNIQUE,
  image_url   VARCHAR(255) NULL,
  color       VARCHAR(9)   NULL,
  sort_order  INT NOT NULL DEFAULT 0,
  active      TINYINT(1) NOT NULL DEFAULT 1,
  KEY idx_cat_parent (parent_id),
  CONSTRAINT fk_cat_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id      INT UNSIGNED NOT NULL,       -- categoria "folha"
  sku              VARCHAR(60)  NULL UNIQUE,
  name             VARCHAR(160) NOT NULL,
  slug             VARCHAR(190) NOT NULL UNIQUE,
  description      TEXT NULL,
  weight_label     VARCHAR(40)  NULL,           -- "1 kg", "500 g"
  price_cents      INT UNSIGNED NOT NULL,
  old_price_cents  INT UNSIGNED NULL,
  stock_qty        INT UNSIGNED NOT NULL DEFAULT 0,
  is_new           TINYINT(1) NOT NULL DEFAULT 0,
  active           TINYINT(1) NOT NULL DEFAULT 1,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_prod_cat (category_id, active),
  KEY idx_prod_name (name),
  CONSTRAINT fk_prod_cat FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_images (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id  BIGINT UNSIGNED NOT NULL,
  url         VARCHAR(255) NOT NULL,
  sort_order  INT NOT NULL DEFAULT 0,
  KEY idx_img_prod (product_id, sort_order),
  CONSTRAINT fk_img_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_reviews (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id  BIGINT UNSIGNED NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  rating      TINYINT UNSIGNED NOT NULL,      -- 1 a 5
  comment     TEXT NULL,
  approved    TINYINT(1) NOT NULL DEFAULT 0,  -- moderação pelo painel
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rev_prod (product_id, approved),
  CONSTRAINT fk_rev_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_rev_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wishlist_items (
  user_id     BIGINT UNSIGNED NOT NULL,
  product_id  BIGINT UNSIGNED NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, product_id),
  CONSTRAINT fk_wish_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_wish_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coupons (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code                VARCHAR(40) NOT NULL UNIQUE,
  type                ENUM('percent','fixed') NOT NULL,
  value               INT UNSIGNED NOT NULL,        -- percent: 0-100 | fixed: centavos
  min_subtotal_cents  INT UNSIGNED NOT NULL DEFAULT 0,
  max_uses            INT UNSIGNED NULL,
  used_count          INT UNSIGNED NOT NULL DEFAULT 0,
  starts_at           DATETIME NULL,
  ends_at             DATETIME 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;

-- ------------------------------------------------------------- carrinho ----
-- Carrinho no servidor: app e web veem o mesmo carrinho.
CREATE TABLE IF NOT EXISTS carts (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL UNIQUE,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cart_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cart_items (
  cart_id     BIGINT UNSIGNED NOT NULL,
  product_id  BIGINT UNSIGNED NOT NULL,
  qty         SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (cart_id, product_id),
  CONSTRAINT fk_ci_cart FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
  CONSTRAINT fk_ci_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------- pedidos ---
CREATE TABLE IF NOT EXISTS orders (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code                VARCHAR(20) NULL UNIQUE,         -- GM000123
  user_id             BIGINT UNSIGNED NOT NULL,
  address_id          BIGINT UNSIGNED NULL,
  delivery_address    JSON NOT NULL,                   -- snapshot: o endereço não muda depois
  channel             ENUM('android','ios','web') NOT NULL DEFAULT 'web',
  status              ENUM('pending_payment','paid','packed','shipped','out_for_delivery',
                           'delivered','cancelled','refunded') NOT NULL DEFAULT 'pending_payment',
  subtotal_cents      INT UNSIGNED NOT NULL,
  discount_cents      INT UNSIGNED NOT NULL DEFAULT 0,
  delivery_fee_cents  INT UNSIGNED NOT NULL DEFAULT 0,
  total_cents         INT UNSIGNED NOT NULL,
  coupon_id           INT UNSIGNED NULL,
  notes               VARCHAR(255) NULL,
  idempotency_key     VARCHAR(64)  NULL,               -- evita pedido duplicado (duplo toque)
  paid_at             DATETIME NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_orders_idem (user_id, idempotency_key),
  KEY idx_orders_user (user_id, created_at),
  KEY idx_orders_status (status, created_at),
  CONSTRAINT fk_orders_user    FOREIGN KEY (user_id)    REFERENCES users(id),
  CONSTRAINT fk_orders_address FOREIGN KEY (address_id) REFERENCES addresses(id) ON DELETE SET NULL,
  CONSTRAINT fk_orders_coupon  FOREIGN KEY (coupon_id)  REFERENCES coupons(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id           BIGINT UNSIGNED NOT NULL,
  product_id         BIGINT UNSIGNED NOT NULL,
  product_name       VARCHAR(160) NOT NULL,          -- snapshot do nome
  unit_price_cents   INT UNSIGNED NOT NULL,          -- snapshot do preço
  qty                SMALLINT UNSIGNED NOT NULL,
  line_total_cents   INT UNSIGNED NOT NULL,
  KEY idx_items_order (order_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_items_prod  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_status_history (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id       BIGINT UNSIGNED NOT NULL,
  status         ENUM('pending_payment','paid','packed','shipped','out_for_delivery',
                      'delivered','cancelled','refunded') NOT NULL,
  note           VARCHAR(255) NULL,
  actor_user_id  BIGINT UNSIGNED NULL,           -- NULL = sistema (cron, webhook)
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_hist_order (order_id, created_at),
  CONSTRAINT fk_hist_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------- pagamentos -
-- Uma linha por tentativa de pagamento. Nunca guardar número/CVV de cartão.
CREATE TABLE IF NOT EXISTS payments (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id        BIGINT UNSIGNED NOT NULL,
  method          ENUM('pix','credit_card','wallet','cash_on_delivery') NOT NULL,
  provider        VARCHAR(30)  NOT NULL,           -- efi | manual | ... (definido pelo cliente)
  provider_ref    VARCHAR(120) NOT NULL,           -- txid do PIX / id da cobrança no gateway
  status          ENUM('pending','paid','expired','cancelled','failed','refunded') NOT NULL DEFAULT 'pending',
  amount_cents    INT UNSIGNED NOT NULL,
  pix_copy_paste  TEXT NULL,
  pix_qr_image    MEDIUMTEXT NULL,                 -- base64 do QR Code
  pix_expires_at  DATETIME NULL,
  end_to_end_id   VARCHAR(40) NULL,                -- E2E do PIX: chave de conciliação bancária
  card_brand      VARCHAR(20) NULL,
  card_last4      CHAR(4) NULL,
  installments    TINYINT UNSIGNED NULL,
  paid_at         DATETIME NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payment_provider_ref (provider, provider_ref),
  KEY idx_payments_order (order_id),
  KEY idx_payments_reconcile (method, status, pix_expires_at),
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Saldo da loja (carteira). Saldo = soma(credit) - soma(debit).
CREATE TABLE IF NOT EXISTS wallet_transactions (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      BIGINT UNSIGNED NOT NULL,
  type         ENUM('credit','debit') NOT NULL,
  amount_cents INT UNSIGNED NOT NULL,
  reason       VARCHAR(40)  NOT NULL,              -- refund, gift_card, order_payment...
  order_id     BIGINT UNSIGNED NULL,
  description  VARCHAR(190) NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_wallet_user (user_id, created_at),
  CONSTRAINT fk_wallet_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Caixa de entrada dos webhooks do banco/PSP (idempotência).
CREATE TABLE IF NOT EXISTS bank_events (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider     VARCHAR(30)  NOT NULL,
  external_id  VARCHAR(120) NOT NULL,             -- E2E ou txid+horário: evita processar 2x
  event_type   VARCHAR(40)  NOT NULL,
  payload      JSON NULL,
  received_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_bank_event (provider, external_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------- alertas e notificações --
-- Alertas do painel admin. O painel faz polling em ?r=admin/alerts&after=ID.
CREATE TABLE IF NOT EXISTS admin_alerts (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type        VARCHAR(40)  NOT NULL,              -- payment_received | paid_after_cancel | ...
  title       VARCHAR(120) NOT NULL,
  body        VARCHAR(255) NOT NULL,
  order_id    BIGINT UNSIGNED NULL,
  payment_id  BIGINT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_alerts_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Histórico de notificações do cliente (tela "Notificações" do app).
CREATE TABLE IF NOT EXISTS notifications (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  title       VARCHAR(120) NOT NULL,
  body        VARCHAR(255) NOT NULL,
  data        JSON NULL,
  read_at     DATETIME NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_notif_user (user_id, created_at),
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tokens FCM dos celulares (cliente e lojista).
CREATE TABLE IF NOT EXISTS device_tokens (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  platform    ENUM('android','ios','web') NOT NULL,
  push_token  VARCHAR(255) NOT NULL UNIQUE,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_devices_user (user_id),
  CONSTRAINT fk_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- auditoria
CREATE TABLE IF NOT EXISTS audit_log (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NULL,
  action      VARCHAR(60) NOT NULL,              -- order.status, payment.confirm, product.upsert...
  entity      VARCHAR(40) NOT NULL,
  entity_id   BIGINT UNSIGNED NULL,
  data        JSON NULL,
  ip          VARCHAR(45) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_entity (entity, entity_id),
  KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------ parâmetros ----
-- Valores padrão. Ajuste pelo painel ou por SQL. Valores em centavos.
INSERT IGNORE INTO settings (`key`, `value`) VALUES
  ('store_name', 'GreenMart'),
  ('currency', 'BRL'),
  ('delivery_fee_cents', '2000'),          -- taxa de entrega: R$ 20,00
  ('free_delivery_min_cents', '20000'),    -- frete grátis a partir de R$ 200,00
  ('pix_expiration_seconds', '3600');      -- PIX expira em 1 hora
