-- GoDoIt Database Schema v2
-- Adds custom columns and task color support

CREATE DATABASE IF NOT EXISTS ed0bd15_GoDo
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE ed0bd15_GoDo;

-- ── Users ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            INT           AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(255)  NOT NULL,
  email         VARCHAR(255)  NOT NULL UNIQUE,
  password_hash VARCHAR(255)  NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Boards ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS boards (
  id          INT           AUTO_INCREMENT PRIMARY KEY,
  user_id     INT           NOT NULL,
  title       VARCHAR(255)  NOT NULL,
  description TEXT,
  share_token VARCHAR(64)   NOT NULL UNIQUE,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Columns (custom per-board) ────────────────────────────────
CREATE TABLE IF NOT EXISTS board_columns (
  id          INT           AUTO_INCREMENT PRIMARY KEY,
  board_id    INT           NOT NULL,
  title       VARCHAR(255)  NOT NULL,
  color       VARCHAR(7)    NOT NULL DEFAULT '#8b5cf6',
  position    INT           NOT NULL DEFAULT 0,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Tasks ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tasks (
  id          INT           AUTO_INCREMENT PRIMARY KEY,
  board_id    INT           NOT NULL,
  column_id   INT           NOT NULL,
  title       VARCHAR(255)  NOT NULL,
  description TEXT,
  priority    TINYINT       NOT NULL DEFAULT 5,
  color       VARCHAR(7)    NOT NULL DEFAULT '#8b5cf6',
  position    INT           NOT NULL DEFAULT 0,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (board_id)  REFERENCES boards(id) ON DELETE CASCADE,
  FOREIGN KEY (column_id) REFERENCES board_columns(id) ON DELETE CASCADE,
  CONSTRAINT chk_priority CHECK (priority >= 1 AND priority <= 10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Indexes ───────────────────────────────────────────────────
CREATE INDEX idx_boards_share_token   ON boards(share_token);
CREATE INDEX idx_columns_board        ON board_columns(board_id, position);
CREATE INDEX idx_tasks_board_column   ON tasks(board_id, column_id, position);