-- migrations/0007_etapes.sql
-- Phase 9: Etapes

-- +goose Up

CREATE TABLE etapes (
    id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    tablo_id    uuid        NOT NULL REFERENCES tablos(id) ON DELETE CASCADE,
    title       text        NOT NULL,
    description text,
    position    integer     NOT NULL DEFAULT 100,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX etapes_tablo_id_position_idx ON etapes(tablo_id, position);

ALTER TABLE tasks
    ADD COLUMN etape_id uuid REFERENCES etapes(id) ON DELETE SET NULL;

CREATE INDEX tasks_tablo_id_etape_id_idx ON tasks(tablo_id, etape_id);

-- +goose Down

DROP INDEX IF EXISTS tasks_tablo_id_etape_id_idx;
ALTER TABLE tasks DROP COLUMN IF EXISTS etape_id;
DROP TABLE IF EXISTS etapes;