-- ============================================================
--  Setu Ledger — Full Database Schema
--  Run this on a fresh MySQL instance to set everything up.
--  The Express server also auto-runs these on boot.
-- ============================================================

CREATE DATABASE IF NOT EXISTS setu;
USE setu;

-- ── 1. Users ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id                   INT AUTO_INCREMENT PRIMARY KEY,
  email                VARCHAR(255)  NOT NULL UNIQUE,
  password             VARCHAR(255)  NOT NULL,
  full_name            VARCHAR(255)  NULL,
  phone                VARCHAR(20)   NULL,
  role                 VARCHAR(50)   NOT NULL DEFAULT 'user',
  is_verified          TINYINT(1)    NOT NULL DEFAULT 0,
  verification_token   VARCHAR(255)  NULL,
  reset_token          VARCHAR(255)  NULL,
  reset_token_expires  DATETIME      NULL,
  plan                 ENUM('free_trial','active','suspended') NOT NULL DEFAULT 'free_trial',
  trial_ends_at        DATETIME      NULL,
  plan_expires_at      DATETIME      NULL,
  created_at           TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payment_proofs (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT       NOT NULL,
  image_url     TEXT      NOT NULL,
  status        ENUM('pending','approved','rejected') DEFAULT 'pending',
  note          VARCHAR(255) NULL,
  submitted_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  reviewed_at   DATETIME  NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payment_qr (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  image_url   TEXT NOT NULL,
  label       VARCHAR(100) NULL,
  is_active   TINYINT(1) DEFAULT 1,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ── 2. Transactions ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS transactions (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  user_id         INT             NOT NULL,
  type            ENUM('income','outcome') NOT NULL,
  category        VARCHAR(100)    NOT NULL,
  amount          DECIMAL(15, 2)  NOT NULL,
  date            DATE            NOT NULL,
  description     VARCHAR(255)    NULL,
  payment_method  VARCHAR(100)    NULL,
  created_at      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ── 3. Budgets ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS budgets (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT             NOT NULL,
  category      VARCHAR(100)    NOT NULL,
  limit_amount  DECIMAL(15, 2)  NOT NULL,
  created_at    TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user_category (user_id, category)
);

-- ── 4. Goals ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS goals (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT             NOT NULL,
  name        VARCHAR(100)    NOT NULL,
  target      DECIMAL(15, 2)  NOT NULL,
  current     DECIMAL(15, 2)  DEFAULT 0.00,
  date        DATE            NOT NULL,
  color       VARCHAR(50)     NOT NULL,
  created_at  TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ── 5. Messages ──────────────────────────────────────────────
-- receiver_id = NULL means broadcast to all users
CREATE TABLE IF NOT EXISTS messages (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  sender_id     INT     NOT NULL,
  receiver_id   INT     NULL,
  message_text  TEXT    NOT NULL,
  is_broadcast  BOOLEAN DEFAULT FALSE,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sender_id)   REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ── Migration helper (safe to run on existing DB) ────────────
-- Run these if you created the DB before the email-auth update.
-- NOTE: ALTER TABLE ... ADD COLUMN IF NOT EXISTS requires MySQL 8.0.3+
-- If you get "Error 1064", remove "IF NOT EXISTS" and run one at a time.
-- If you get "Error 1060 Duplicate column", that column already exists — skip it.
--
-- ALTER TABLE users ADD COLUMN is_verified         TINYINT(1)   NOT NULL DEFAULT 0;
-- ALTER TABLE users ADD COLUMN verification_token  VARCHAR(255) NULL;
-- ALTER TABLE users ADD COLUMN reset_token         VARCHAR(255) NULL;
-- ALTER TABLE users ADD COLUMN reset_token_expires DATETIME     NULL;
-- ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;
-- ALTER TABLE users ADD COLUMN plan ENUM('free_trial','active','suspended') NOT NULL DEFAULT 'free_trial';
-- ALTER TABLE users ADD COLUMN trial_ends_at DATETIME NULL;
-- ALTER TABLE users ADD COLUMN plan_expires_at DATETIME NULL;
-- UPDATE users SET trial_ends_at = DATE_ADD(created_at, INTERVAL 7 DAY) WHERE role <> 'admin' AND trial_ends_at IS NULL;
-- UPDATE users SET phone = 'admin' WHERE role = 'admin' AND (phone IS NULL OR phone = '');
-- UPDATE users SET is_verified = 1 WHERE role = 'admin';