-- Simplinion Tasks API – Database Schema
-- MySQL 8.0+

SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────────────────────
-- Customers
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS customers (
    id           CHAR(36)     NOT NULL,
    name         VARCHAR(255) NOT NULL,
    email        VARCHAR(255) DEFAULT NULL,
    company_name VARCHAR(255) DEFAULT NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- Tasks (Goals and regular tasks share this table; goal_id creates the hierarchy)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tasks (
    id                         CHAR(36)     NOT NULL,
    title                      VARCHAR(255) NOT NULL,
    description                TEXT         DEFAULT NULL,
    status                     VARCHAR(50)  NOT NULL,
    priority_main              CHAR(1)      NOT NULL COMMENT 'A|B|C|D|E',
    priority_sub               INT          NOT NULL DEFAULT 1,
    goal_id                    CHAR(36)     DEFAULT NULL COMMENT 'Self-referencing FK for goals',
    deadline                   DATETIME     DEFAULT NULL,
    estimation_value           DECIMAL(10, 2) DEFAULT NULL,
    estimation_unit            ENUM('hours', 'days', 'months', 'story_points') DEFAULT NULL,
    planned_start_date         DATE         DEFAULT NULL,
    planned_end_date           DATE         DEFAULT NULL,
    actual_started_at          DATETIME     DEFAULT NULL,
    actual_time_logged_minutes INT          NOT NULL DEFAULT 0,
    assignee_id                VARCHAR(100) DEFAULT NULL,
    created_at                 DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                 DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_task_goal FOREIGN KEY (goal_id) REFERENCES tasks (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- Task ↔ Groups  (many-to-many via a simple join table)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS task_groups (
    task_id    CHAR(36)     NOT NULL,
    group_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (task_id, group_name),
    CONSTRAINT fk_tg_task FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- Task ↔ Customers  (many-to-many)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS task_customers (
    task_id     CHAR(36) NOT NULL,
    customer_id CHAR(36) NOT NULL,
    PRIMARY KEY (task_id, customer_id),
    CONSTRAINT fk_tc_task     FOREIGN KEY (task_id)     REFERENCES tasks     (id) ON DELETE CASCADE,
    CONSTRAINT fk_tc_customer FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- Subtasks
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS subtasks (
    id           CHAR(36)     NOT NULL,
    task_id      CHAR(36)     NOT NULL,
    title        VARCHAR(255) NOT NULL,
    is_completed TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    CONSTRAINT fk_subtask_task FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- Checklist items (nested inside subtasks)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS checklist_items (
    id         VARCHAR(50) NOT NULL,
    subtask_id CHAR(36)    NOT NULL,
    text       TEXT        NOT NULL,
    is_done    TINYINT(1)  NOT NULL DEFAULT 0,
    PRIMARY KEY (id, subtask_id),
    CONSTRAINT fk_ci_subtask FOREIGN KEY (subtask_id) REFERENCES subtasks (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- Time Logs
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS time_logs (
    id               CHAR(36)  NOT NULL,
    task_id          CHAR(36)  NOT NULL,
    duration_minutes INT       NOT NULL,
    note             TEXT      DEFAULT NULL,
    logged_at        DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_tl_task FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
