-- Slice 8b-3 · Services PM Plan (first real consumer of the Work Templates engine 8b-T).
-- A plan says: for THIS project, apply THIS PM template to THAT asset at THIS cadence.
-- The generator turns due items into WorkInstances (against the asset) for the FS/FT queue.

CREATE TABLE IF NOT EXISTS service_pm_plans (
    c0d            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id     INT NOT NULL,
    title          VARCHAR(200) NOT NULL,
    description    TEXT NULL,
    planned_start  DATE NULL,
    planned_end    DATE NULL,
    status         ENUM('PLANNED','ACTIVE','CLOSED','CANCELLED') NOT NULL DEFAULT 'PLANNED',
    activated_on   TIMESTAMP NULL,
    closed_on      TIMESTAMP NULL,
    created_by     INT NOT NULL DEFAULT 0,
    created_by_name VARCHAR(120) NOT NULL DEFAULT '',
    created_on     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by      INT NOT NULL DEFAULT 0,
    edited_on      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted        TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_project_status (project_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_pm_plan_items (
    c0d                 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    plan_id             INT NOT NULL,
    asset_id            INT NOT NULL,
    template_id         INT NOT NULL,
    -- Cadence controls how far next_due advances after each generation.
    cadence             ENUM('DAILY','WEEKLY','FORTNIGHTLY','MONTHLY','QUARTERLY','HALFYEARLY','YEARLY','ON_DEMAND') NOT NULL DEFAULT 'MONTHLY',
    next_due            DATE NULL,
    last_generated_on   TIMESTAMP NULL,
    -- Nullable — a plan can pre-nominate a Field Technician, or leave unclaimed for the site pool.
    default_assignee_id INT NOT NULL DEFAULT 0,
    default_assignee_name VARCHAR(120) NOT NULL DEFAULT '',
    is_active           TINYINT(1) NOT NULL DEFAULT 1,
    notes               VARCHAR(500) NOT NULL DEFAULT '',
    created_on          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_on           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted             TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_plan_asset_template (plan_id, asset_id, template_id, deleted),
    KEY ix_next_due (next_due, is_active),
    KEY ix_plan (plan_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
