-- RetroBoard MySQL Schema
-- All IDs are UUID v4 (CHAR 36)
-- Run once on a fresh database

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS organizations (
    id         CHAR(36)     NOT NULL PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    slug       VARCHAR(100) NOT NULL UNIQUE,
    logo_url   VARCHAR(500)          DEFAULT NULL,
    plan       ENUM('free','pro','enterprise') NOT NULL DEFAULT 'free',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id            CHAR(36)     NOT NULL PRIMARY KEY,
    org_id        CHAR(36)     NOT NULL,
    email         VARCHAR(255) NOT NULL UNIQUE,
    name          VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url    VARCHAR(500)          DEFAULT NULL,
    role          ENUM('super_admin','manager','member') NOT NULL DEFAULT 'member',
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at DATETIME              DEFAULT NULL,
    FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS refresh_tokens (
    id         CHAR(36)     NOT NULL PRIMARY KEY,
    user_id    CHAR(36)     NOT NULL,
    token_hash VARCHAR(255) NOT NULL UNIQUE,
    expires_at DATETIME     NOT NULL,
    revoked    TINYINT(1)   NOT NULL DEFAULT 0,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS teams (
    id          CHAR(36)     NOT NULL PRIMARY KEY,
    org_id      CHAR(36)     NOT NULL,
    name        VARCHAR(255) NOT NULL,
    description TEXT                  DEFAULT NULL,
    created_by  CHAR(36)     NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id)     REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_members (
    id        CHAR(36)  NOT NULL PRIMARY KEY,
    team_id   CHAR(36)  NOT NULL,
    user_id   CHAR(36)  NOT NULL,
    role      ENUM('manager','member') NOT NULL DEFAULT 'member',
    joined_at DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_team_user (team_id, user_id),
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS retro_templates (
    id          CHAR(36)     NOT NULL PRIMARY KEY,
    org_id      CHAR(36)              DEFAULT NULL,
    name        VARCHAR(255) NOT NULL,
    description TEXT                  DEFAULT NULL,
    columns_json LONGTEXT    NOT NULL,
    is_default  TINYINT(1)   NOT NULL DEFAULT 0,
    created_by  CHAR(36)              DEFAULT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id)    REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS retro_sessions (
    id               CHAR(36)     NOT NULL PRIMARY KEY,
    org_id           CHAR(36)     NOT NULL,
    team_id          CHAR(36)     NOT NULL,
    template_id      CHAR(36)     NOT NULL,
    title            VARCHAR(255) NOT NULL,
    description      TEXT                  DEFAULT NULL,
    code             CHAR(8)      NOT NULL UNIQUE,
    phase            ENUM('waiting','active','voting','actions','closed') NOT NULL DEFAULT 'waiting',
    is_anonymous     TINYINT(1)   NOT NULL DEFAULT 0,
    votes_per_person TINYINT      NOT NULL DEFAULT 3,
    facilitator_id   CHAR(36)     NOT NULL,
    scheduled_for    DATETIME              DEFAULT NULL,
    started_at       DATETIME              DEFAULT NULL,
    closed_at        DATETIME              DEFAULT NULL,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id)         REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id)        REFERENCES teams(id) ON DELETE CASCADE,
    FOREIGN KEY (template_id)    REFERENCES retro_templates(id),
    FOREIGN KEY (facilitator_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS retro_cards (
    id         CHAR(36)  NOT NULL PRIMARY KEY,
    session_id CHAR(36)  NOT NULL,
    column_id  VARCHAR(100) NOT NULL,
    author_id  CHAR(36)           DEFAULT NULL,
    content    TEXT      NOT NULL,
    is_hidden  TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES retro_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id)  REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS card_votes (
    id         CHAR(36)  NOT NULL PRIMARY KEY,
    card_id    CHAR(36)  NOT NULL,
    user_id    CHAR(36)  NOT NULL,
    created_at DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_card_user (card_id, user_id),
    FOREIGN KEY (card_id) REFERENCES retro_cards(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS action_items (
    id          CHAR(36)     NOT NULL PRIMARY KEY,
    session_id  CHAR(36)     NOT NULL,
    card_id     CHAR(36)              DEFAULT NULL,
    title       VARCHAR(500) NOT NULL,
    description TEXT                  DEFAULT NULL,
    owner_id    CHAR(36)              DEFAULT NULL,
    due_date    DATE                  DEFAULT NULL,
    status      ENUM('open','in_progress','done','cancelled') NOT NULL DEFAULT 'open',
    created_by  CHAR(36)     NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES retro_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (card_id)    REFERENCES retro_cards(id) ON DELETE SET NULL,
    FOREIGN KEY (owner_id)   REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS session_participants (
    id         CHAR(36)  NOT NULL PRIMARY KEY,
    session_id CHAR(36)  NOT NULL,
    user_id    CHAR(36)  NOT NULL,
    joined_at  DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    votes_used TINYINT   NOT NULL DEFAULT 0,
    UNIQUE KEY uq_session_user (session_id, user_id),
    FOREIGN KEY (session_id) REFERENCES retro_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id         CHAR(36)  NOT NULL PRIMARY KEY,
    user_id    CHAR(36)  NOT NULL,
    type       ENUM('session_created','phase_changed','action_assigned','action_due','session_reminder') NOT NULL,
    payload    LONGTEXT  NOT NULL,
    is_read    TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Built-in templates (org_id NULL = global)
INSERT IGNORE INTO retro_templates (id, org_id, name, description, columns_json, is_default, created_by) VALUES
(
    '00000000-0000-4000-a000-000000000001', NULL,
    'Start / Stop / Continue', 'Classic 3-column retro',
    '[{"id":"start","name":"Start","color":"#22c55e","order":1},{"id":"stop","name":"Stop","color":"#ef4444","order":2},{"id":"continue","name":"Continue","color":"#3b82f6","order":3}]',
    1, NULL
),
(
    '00000000-0000-4000-a000-000000000002', NULL,
    'Went Well / Didn\'t / Improvement / Actions / Lessons', '5-column comprehensive retro',
    '[{"id":"well","name":"What went well","color":"#22c55e","order":1},{"id":"didnt","name":"What didn\'t go well","color":"#ef4444","order":2},{"id":"improvement","name":"What needs improvement","color":"#f59e0b","order":3},{"id":"actions","name":"Action Items","color":"#3b82f6","order":4},{"id":"lessons","name":"Lessons Learned","color":"#8b5cf6","order":5}]',
    0, NULL
),
(
    '00000000-0000-4000-a000-000000000003', NULL,
    'Mad / Sad / Glad', 'Emotion-focused retro',
    '[{"id":"mad","name":"Mad","color":"#ef4444","order":1},{"id":"sad","name":"Sad","color":"#6366f1","order":2},{"id":"glad","name":"Glad","color":"#22c55e","order":3}]',
    0, NULL
),
(
    '00000000-0000-4000-a000-000000000004', NULL,
    '4Ls', 'Liked / Learned / Lacked / Longed for',
    '[{"id":"liked","name":"Liked","color":"#22c55e","order":1},{"id":"learned","name":"Learned","color":"#3b82f6","order":2},{"id":"lacked","name":"Lacked","color":"#ef4444","order":3},{"id":"longed","name":"Longed for","color":"#f59e0b","order":4}]',
    0, NULL
);