-- Migration 051: Tasks module (dashboard-key scoped accounts, plans, projects, tasks)
-- Aligns with plans/tasks/01_api_database_tasks_admin.yaml

CREATE TABLE IF NOT EXISTS ftt_task_accounts (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    key_fingerprint CHAR(64)      NOT NULL,
    label           VARCHAR(128)  DEFAULT NULL,
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ftt_task_accounts_fp (key_fingerprint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_task_plans (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id    BIGINT        NOT NULL,
    plan_id       VARCHAR(128)  NOT NULL,
    title         VARCHAR(255)  NOT NULL,
    source_path   VARCHAR(512)  DEFAULT NULL,
    yaml_body     MEDIUMTEXT    NOT NULL,
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ftt_task_plans_account_plan (account_id, plan_id),
    CONSTRAINT fk_ftt_task_plans_account
        FOREIGN KEY (account_id) REFERENCES ftt_task_accounts(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_task_projects (
    id                 BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id         BIGINT        NOT NULL,
    client_id          VARCHAR(128)  NOT NULL,
    name               VARCHAR(255)  NOT NULL,
    description        TEXT,
    parent_client_id   VARCHAR(128)  DEFAULT NULL,
    status             VARCHAR(32)   NOT NULL DEFAULT 'active',
    json_extra         JSON          DEFAULT NULL,
    created_ms         BIGINT        NOT NULL,
    modified_ms        BIGINT        NOT NULL,
    UNIQUE KEY uq_ftt_task_projects_account_client (account_id, client_id),
    CONSTRAINT fk_ftt_task_projects_account
        FOREIGN KEY (account_id) REFERENCES ftt_task_accounts(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_tasks (
    id                 BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id         BIGINT        NOT NULL,
    client_id          VARCHAR(128)  NOT NULL,
    project_client_id  VARCHAR(128)  DEFAULT NULL,
    title              VARCHAR(512)  NOT NULL,
    description        MEDIUMTEXT,
    type               VARCHAR(32)   NOT NULL DEFAULT 'task',
    priority           VARCHAR(32)   NOT NULL,
    status             VARCHAR(64)   NOT NULL,
    due_date           DATE          DEFAULT NULL,
    tags_json          JSON          DEFAULT NULL,
    recurrence_json    JSON          DEFAULT NULL,
    created_ms         BIGINT        NOT NULL,
    modified_ms        BIGINT        NOT NULL,
    completed_ms       BIGINT        DEFAULT NULL,
    UNIQUE KEY uq_ftt_tasks_account_client (account_id, client_id),
    CONSTRAINT fk_ftt_tasks_account
        FOREIGN KEY (account_id) REFERENCES ftt_task_accounts(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;