-- schema.sql — Struktur dasar database Kelas WA + Midtrans + ManyChat bridge

CREATE TABLE IF NOT EXISTS classes (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(255) NOT NULL,
  price     INT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS consumers (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(255) NOT NULL,
  whatsapp  VARCHAR(30) NOT NULL,
  email     VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uniq_whatsapp (whatsapp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  order_code  VARCHAR(64) NOT NULL,
  consumer_id INT NOT NULL,
  class_id    INT NOT NULL,
  amount      INT NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  payment_url TEXT,
  midtrans_raw MEDIUMTEXT,
  created_at  DATETIME NOT NULL,
  paid_at     DATETIME DEFAULT NULL,
  KEY idx_order_code (order_code),
  KEY idx_status (status),
  CONSTRAINT fk_orders_consumer FOREIGN KEY (consumer_id) REFERENCES consumers(id),
  CONSTRAINT fk_orders_class FOREIGN KEY (class_id) REFERENCES classes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL UNIQUE,
  value TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
