BEGIN;

ALTER TABLE ai_insight_snapshots
    ADD COLUMN IF NOT EXISTS section_id text NOT NULL DEFAULT 'full';
ALTER TABLE ai_insight_jobs
    ADD COLUMN IF NOT EXISTS section_id text NOT NULL DEFAULT 'full';
ALTER TABLE growth_coach_profiles
    ADD COLUMN IF NOT EXISTS changed_fields jsonb NOT NULL DEFAULT '[]'::jsonb;

CREATE TABLE IF NOT EXISTS ai_generation_holds (
    terminal_id bigint PRIMARY KEY REFERENCES terminals(id) ON DELETE CASCADE,
    merchant_id bigint NOT NULL REFERENCES merchants(id),
    reason text NOT NULL CHECK (reason IN ('ONBOARDING_REQUIRED')),
    created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE ai_insight_jobs DROP CONSTRAINT IF EXISTS ai_insight_jobs_trigger_reason_check;
ALTER TABLE ai_insight_jobs ADD CONSTRAINT ai_insight_jobs_trigger_reason_check
    CHECK (trigger_reason IN ('BACKFILL','NIGHTLY','IMPORT','CALENDAR','ANOMALY','MANUAL','PROFILE'));

ALTER TABLE ai_insight_snapshots DROP CONSTRAINT IF EXISTS ai_insight_snapshots_section_id_check;
ALTER TABLE ai_insight_snapshots ADD CONSTRAINT ai_insight_snapshots_section_id_check
    CHECK (section_id IN ('full','past','latest-observed','future-7-days','future-30-days','future-90-days','findings','options'));
ALTER TABLE ai_insight_jobs DROP CONSTRAINT IF EXISTS ai_insight_jobs_section_id_check;
ALTER TABLE ai_insight_jobs ADD CONSTRAINT ai_insight_jobs_section_id_check
    CHECK (section_id IN ('full','past','latest-observed','future-7-days','future-30-days','future-90-days','findings','options'));

DROP INDEX IF EXISTS ai_insight_manual_refresh_idx;
CREATE TABLE IF NOT EXISTS ai_insight_refresh_attempts (
    id bigserial PRIMARY KEY,
    job_id text NOT NULL REFERENCES ai_insight_jobs(id) ON DELETE CASCADE,
    merchant_id bigint NOT NULL REFERENCES merchants(id),
    terminal_source_key text,
    module text NOT NULL,
    section_id text NOT NULL CHECK (section_id IN ('past','latest-observed','future-7-days','future-30-days','future-90-days','findings','options')),
    tehran_refresh_day date NOT NULL,
    accepted_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE(job_id,section_id)
);
CREATE INDEX IF NOT EXISTS ai_insight_refresh_scope_idx
    ON ai_insight_refresh_attempts(merchant_id,coalesce(terminal_source_key,''),module,section_id,tehran_refresh_day,accepted_at DESC);

ALTER TABLE growth_coach_recommendations
    ADD COLUMN IF NOT EXISTS subtasks jsonb NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE growth_coach_recommendations
    ADD COLUMN IF NOT EXISTS event_ids jsonb NOT NULL DEFAULT '[]'::jsonb;

CREATE TABLE IF NOT EXISTS monthly_goals (
    id text PRIMARY KEY,
    recommendation_id text NOT NULL UNIQUE REFERENCES growth_coach_recommendations(id),
    merchant_id bigint NOT NULL REFERENCES merchants(id),
    terminal_id bigint NOT NULL REFERENCES terminals(id),
    terminal_source_key text NOT NULL,
    profile_id text NOT NULL REFERENCES growth_coach_profiles(id),
    title text NOT NULL,
    expected_impact text NOT NULL,
    status text NOT NULL CHECK (status IN ('ACTIVE','COMPLETED','MISSED','ABANDONED')),
    metric_id text NOT NULL,
    metric_direction text NOT NULL CHECK (metric_direction IN ('INCREASE','DECREASE')),
    baseline numeric(30,6) NOT NULL,
    target numeric(30,6) NOT NULL,
    current_value numeric(30,6) NOT NULL,
    sample_size bigint NOT NULL,
    baseline_watermark text,
    latest_watermark text,
    progress_bps integer NOT NULL DEFAULT 0 CHECK (progress_bps BETWEEN 0 AND 10000),
    event_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
    deadline date NOT NULL,
    actor_user_id bigint NOT NULL REFERENCES merchant_users(id),
    accepted_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz,
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (target <> baseline)
);
CREATE INDEX IF NOT EXISTS monthly_goals_scope_idx
    ON monthly_goals(merchant_id,terminal_id,status,deadline);

CREATE TABLE IF NOT EXISTS goal_subtasks (
    id text PRIMARY KEY,
    goal_id text NOT NULL REFERENCES monthly_goals(id) ON DELETE CASCADE,
    merchant_id bigint NOT NULL REFERENCES merchants(id),
    position smallint NOT NULL CHECK (position BETWEEN 1 AND 7),
    title text NOT NULL,
    status text NOT NULL CHECK (status IN ('TODO','DONE')),
    deadline date NOT NULL,
    event_id text,
    completed_by bigint REFERENCES merchant_users(id),
    completed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE(goal_id,position)
);
CREATE INDEX IF NOT EXISTS goal_subtasks_goal_idx ON goal_subtasks(goal_id,position);

CREATE TABLE IF NOT EXISTS goal_deadline_proposals (
    id text PRIMARY KEY,
    goal_id text NOT NULL REFERENCES monthly_goals(id) ON DELETE CASCADE,
    subtask_id text NOT NULL REFERENCES goal_subtasks(id) ON DELETE CASCADE,
    merchant_id bigint NOT NULL REFERENCES merchants(id),
    event_id text NOT NULL,
    current_deadline date NOT NULL,
    proposed_deadline date NOT NULL,
    reason text NOT NULL,
    status text NOT NULL CHECK (status IN ('OFFERED','ACCEPTED','REJECTED','EXPIRED')),
    decided_by bigint REFERENCES merchant_users(id),
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS goal_deadline_proposals_open_idx
    ON goal_deadline_proposals(subtask_id,proposed_deadline) WHERE status='OFFERED';

-- Preserve non-reset merchants' accepted work as legacy goals without inventing subtasks.
INSERT INTO monthly_goals(id,recommendation_id,merchant_id,terminal_id,terminal_source_key,profile_id,title,expected_impact,status,metric_id,metric_direction,baseline,target,current_value,sample_size,baseline_watermark,deadline,actor_user_id,accepted_at,completed_at,updated_at)
SELECT a.id,a.recommendation_id,a.merchant_id,a.terminal_id,a.terminal_source_key,p.profile_id,a.title,r.expected_impact,
       CASE a.status WHEN 'COMPLETED' THEN 'COMPLETED' WHEN 'MISSED' THEN 'MISSED' WHEN 'ABANDONED' THEN 'ABANDONED' ELSE 'ACTIVE' END,
       a.metric_id,a.metric_direction,a.baseline,a.target,coalesce(a.current_value,a.baseline),coalesce(a.sample_size,0),
       coalesce((SELECT source_sha256 FROM import_runs WHERE status='COMPLETED' ORDER BY completed_at DESC LIMIT 1),'no-data'),
       a.deadline,a.actor_user_id,a.created_at,a.completed_at,a.updated_at
FROM golden_steps a
JOIN growth_coach_recommendations r ON r.id=a.recommendation_id
JOIN growth_coach_plans p ON p.id=r.plan_id
WHERE a.action_type='METRIC' AND a.metric_id IS NOT NULL AND a.metric_direction IS NOT NULL
  AND a.baseline IS NOT NULL AND a.target IS NOT NULL AND a.target<>a.baseline
ON CONFLICT DO NOTHING;

-- Repair impossible completions in both the compatibility table and the new goal table.
UPDATE golden_steps
SET status='IN_PROGRESS',completed_at=NULL,updated_at=now()
WHERE status='COMPLETED' AND (
    baseline IS NULL OR target IS NULL OR target=baseline OR current_value IS NULL OR
    NOT EXISTS(SELECT 1 FROM growth_coach_progress_snapshots p WHERE p.action_id=golden_steps.id) OR
    (metric_direction='INCREASE' AND current_value<target) OR
    (metric_direction='DECREASE' AND current_value>target)
);

-- Treat the currently imported dataset as the baseline evidence for repaired or
-- pre-existing compatibility actions. A later watermark is required to evaluate.
INSERT INTO growth_coach_progress_snapshots(action_id,watermark,value,sample_size)
SELECT a.id,
       coalesce((SELECT source_sha256 FROM import_runs WHERE status='COMPLETED' ORDER BY completed_at DESC LIMIT 1),'no-data'),
       coalesce(a.current_value,a.baseline),coalesce(a.sample_size,0)
FROM golden_steps a
WHERE a.action_type='METRIC' AND a.status='IN_PROGRESS' AND a.baseline IS NOT NULL AND a.target IS NOT NULL AND a.target<>a.baseline
ON CONFLICT DO NOTHING;
UPDATE monthly_goals
SET status='ACTIVE',completed_at=NULL,progress_bps=0,updated_at=now()
WHERE status='COMPLETED' AND (
    baseline_watermark IS NULL OR latest_watermark IS NULL OR latest_watermark=baseline_watermark OR
    (metric_direction='INCREASE' AND current_value<target) OR
    (metric_direction='DECREASE' AND current_value>target)
);

INSERT INTO schema_migrations(version) VALUES (8) ON CONFLICT DO NOTHING;
COMMIT;