CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE,
  entra_object_id VARCHAR(100),
  role VARCHAR(50) DEFAULT 'coder',
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE users ADD COLUMN IF NOT EXISTS entra_object_id VARCHAR(100);

CREATE UNIQUE INDEX IF NOT EXISTS users_entra_object_id_unique_idx
ON users (entra_object_id)
WHERE entra_object_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS projects (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  project_type VARCHAR(50) NOT NULL DEFAULT 'unique',
  source_file VARCHAR(255),
  file_hash VARCHAR(64) UNIQUE,
  status VARCHAR(50) DEFAULT 'open',
  created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  updated_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE projects ADD COLUMN IF NOT EXISTS project_type VARCHAR(50) NOT NULL DEFAULT 'unique';
ALTER TABLE projects ALTER COLUMN source_file DROP NOT NULL;
ALTER TABLE projects ALTER COLUMN file_hash DROP NOT NULL;

CREATE INDEX IF NOT EXISTS projects_status_updated_idx
ON projects (status, updated_at DESC, id DESC);

UPDATE projects
SET status = 'open'
WHERE status = 'closed';

UPDATE projects
SET project_type = 'tracker'
WHERE project_type = 'unique'
  AND (name ILIKE '%tracker%' OR source_file ILIKE '%tracker%');

CREATE TABLE IF NOT EXISTS studies (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  wave_name VARCHAR(100),
  source_file VARCHAR(255) NOT NULL,
  file_hash VARCHAR(64) NOT NULL,
  created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  updated_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (project_id, file_hash)
);

CREATE UNIQUE INDEX IF NOT EXISTS studies_project_source_file_unique_idx
ON studies (project_id, LOWER(source_file));

CREATE INDEX IF NOT EXISTS studies_project_created_idx
ON studies (project_id, created_at DESC, id DESC);

CREATE TABLE IF NOT EXISTS question_groups (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  study_id INTEGER NOT NULL REFERENCES studies(id) ON DELETE CASCADE,
  client_key VARCHAR(100) NOT NULL,
  name VARCHAR(255) NOT NULL,
  question_keys TEXT[] NOT NULL DEFAULT '{}',
  expanded BOOLEAN NOT NULL DEFAULT TRUE,
  position INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (study_id, client_key)
);

CREATE INDEX IF NOT EXISTS question_groups_project_study_idx
ON question_groups (project_id, study_id, position, id);

CREATE TABLE IF NOT EXISTS codeframe_files (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  source_file VARCHAR(255) NOT NULL,
  file_hash VARCHAR(64) NOT NULL,
  question_scope TEXT[],
  uploaded_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  updated_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (project_id, file_hash)
);

CREATE TABLE IF NOT EXISTS codeframe_entries (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  code VARCHAR(255) NOT NULL,
  label VARCHAR(255) NOT NULL,
  source_type VARCHAR(20) NOT NULL DEFAULT 'manual',
  source_file_id INTEGER REFERENCES codeframe_files(id) ON DELETE CASCADE,
  question_scope TEXT[],
  updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  updated_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (project_id, code)
);

ALTER TABLE codeframe_entries ADD COLUMN IF NOT EXISTS source_type VARCHAR(20) NOT NULL DEFAULT 'manual';
ALTER TABLE codeframe_entries ADD COLUMN IF NOT EXISTS source_file_id INTEGER REFERENCES codeframe_files(id) ON DELETE CASCADE;
ALTER TABLE codeframe_entries ADD COLUMN IF NOT EXISTS question_scope TEXT[];
ALTER TABLE codeframe_files ADD COLUMN IF NOT EXISTS question_scope TEXT[];

WITH split_entries AS (
  SELECT
    c.project_id,
    BTRIM(codes.value) AS code,
    BTRIM(labels.value) AS label,
    c.source_type,
    c.source_file_id,
    c.updated_by,
    c.updated_at,
    c.created_at
  FROM codeframe_entries c
  JOIN LATERAL regexp_split_to_table(c.code, '[[:space:]]*,[[:space:]]*') WITH ORDINALITY codes(value, position) ON TRUE
  JOIN LATERAL regexp_split_to_table(c.label, '[[:space:]]*,[[:space:]]*') WITH ORDINALITY labels(value, position)
    ON labels.position = codes.position
  WHERE (c.code LIKE '%,%' OR c.label LIKE '%,%')
    AND NULLIF(BTRIM(codes.value), '') IS NOT NULL
    AND NULLIF(BTRIM(labels.value), '') IS NOT NULL
)
INSERT INTO codeframe_entries (project_id, code, label, source_type, source_file_id, updated_by, updated_at, created_at)
SELECT DISTINCT ON (project_id, code)
  project_id, code, label, source_type, source_file_id, updated_by, updated_at, created_at
FROM split_entries
ORDER BY project_id, code, updated_at DESC, created_at DESC
ON CONFLICT (project_id, code) DO NOTHING;

DELETE FROM codeframe_entries
WHERE code LIKE '%,%' OR label LIKE '%,%';

DELETE FROM codeframe_entries older
USING codeframe_entries newer
WHERE older.project_id = newer.project_id
  AND older.code = newer.code
  AND (
    older.updated_at < newer.updated_at
    OR (older.updated_at = newer.updated_at AND older.id < newer.id)
  );

DELETE FROM codeframe_entries older
USING codeframe_entries newer
WHERE older.project_id = newer.project_id
  AND LOWER(BTRIM(older.label)) = LOWER(BTRIM(newer.label))
  AND (
    older.updated_at < newer.updated_at
    OR (older.updated_at = newer.updated_at AND older.id < newer.id)
  );

CREATE UNIQUE INDEX IF NOT EXISTS codeframe_entries_project_code_unique_idx
ON codeframe_entries (project_id, code);

CREATE UNIQUE INDEX IF NOT EXISTS codeframe_entries_project_label_unique_idx
ON codeframe_entries (project_id, LOWER(BTRIM(label)))
WHERE NULLIF(BTRIM(label), '') IS NOT NULL;

CREATE INDEX IF NOT EXISTS codeframe_entries_source_file_idx
ON codeframe_entries (source_file_id);

CREATE INDEX IF NOT EXISTS codeframe_entries_question_scope_idx
ON codeframe_entries USING GIN (question_scope);

CREATE INDEX IF NOT EXISTS codeframe_files_question_scope_idx
ON codeframe_files USING GIN (question_scope);

CREATE TABLE IF NOT EXISTS project_members (
  project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  joined_at TIMESTAMP DEFAULT NOW(),
  last_active_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (project_id, user_id)
);

CREATE UNIQUE INDEX IF NOT EXISTS projects_source_file_unique_idx
ON projects (LOWER(source_file));

CREATE TABLE IF NOT EXISTS records (
  id SERIAL PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  study_id INTEGER REFERENCES studies(id) ON DELETE CASCADE,
  source_file VARCHAR(255),
  respondent_serial VARCHAR(255),
  respondent_id VARCHAR(255),
  question_key VARCHAR(255),
  response_text TEXT NOT NULL,
  label VARCHAR(255),
  code VARCHAR(255),
  status VARCHAR(50) DEFAULT 'uncoded & unlabeled',
  assigned_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  updated_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE records ADD COLUMN IF NOT EXISTS source_file VARCHAR(255);
ALTER TABLE records ADD COLUMN IF NOT EXISTS respondent_serial VARCHAR(255);
ALTER TABLE records ADD COLUMN IF NOT EXISTS respondent_id VARCHAR(255);
ALTER TABLE records ADD COLUMN IF NOT EXISTS question_key VARCHAR(255);
ALTER TABLE records ADD COLUMN IF NOT EXISTS project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE;
ALTER TABLE records ADD COLUMN IF NOT EXISTS study_id INTEGER REFERENCES studies(id) ON DELETE CASCADE;
ALTER TABLE records ADD COLUMN IF NOT EXISTS label VARCHAR(255);
ALTER TABLE records ADD COLUMN IF NOT EXISTS parent_record_id INTEGER REFERENCES records(id) ON DELETE CASCADE;
ALTER TABLE records ADD COLUMN IF NOT EXISTS segment_index INTEGER;
ALTER TABLE records DROP COLUMN IF EXISTS comment;
ALTER TABLE records ALTER COLUMN status SET DEFAULT 'uncoded & unlabeled';

DROP INDEX IF EXISTS records_import_unique_idx;
DROP INDEX IF EXISTS records_project_import_unique_idx;
DROP INDEX IF EXISTS records_study_import_unique_idx;

DELETE FROM records older
USING records newer
WHERE older.study_id = newer.study_id
  AND older.respondent_serial IS NOT DISTINCT FROM newer.respondent_serial
  AND COALESCE(older.respondent_id, '') = COALESCE(newer.respondent_id, '')
  AND older.question_key = newer.question_key
  AND older.parent_record_id IS NULL
  AND newer.parent_record_id IS NULL
  AND older.id < newer.id;

CREATE UNIQUE INDEX records_study_import_unique_idx
ON records (study_id, respondent_serial, (COALESCE(respondent_id, ''::character varying)), question_key)
WHERE study_id IS NOT NULL AND question_key IS NOT NULL AND parent_record_id IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS records_parent_segment_unique_idx
ON records (parent_record_id, segment_index)
WHERE parent_record_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS records_parent_record_idx
ON records (parent_record_id);

CREATE INDEX IF NOT EXISTS records_project_code_label_idx
ON records (project_id, code, label);

CREATE INDEX IF NOT EXISTS records_project_study_question_idx
ON records (project_id, study_id, question_key, COALESCE(parent_record_id, id), segment_index, id);

CREATE INDEX IF NOT EXISTS records_project_parent_status_idx
ON records (project_id, parent_record_id, status);

CREATE INDEX IF NOT EXISTS records_study_parent_status_idx
ON records (study_id, parent_record_id, status);

CREATE INDEX IF NOT EXISTS records_project_code_idx
ON records (project_id, code);

CREATE INDEX IF NOT EXISTS records_project_coded_idx
ON records (project_id, id)
WHERE NULLIF(BTRIM(code), '') IS NOT NULL
  AND NULLIF(BTRIM(label), '') IS NOT NULL;

CREATE INDEX IF NOT EXISTS records_code_values_gin_idx
ON records USING GIN (string_to_array(REPLACE(COALESCE(code, ''), ' ', ''), ','))
WHERE NULLIF(BTRIM(code), '') IS NOT NULL;

CREATE INDEX IF NOT EXISTS records_project_label_normalized_idx
ON records (project_id, LOWER(BTRIM(label)));

UPDATE records
SET status = CASE
  WHEN NULLIF(BTRIM(code), '') IS NULL AND NULLIF(BTRIM(label), '') IS NULL THEN 'uncoded & unlabeled'
  WHEN NULLIF(BTRIM(code), '') IS NULL THEN 'uncoded'
  WHEN NULLIF(BTRIM(label), '') IS NULL THEN 'unlabeled'
  ELSE 'coded'
END
WHERE NOT EXISTS (SELECT 1 FROM records child WHERE child.parent_record_id = records.id)
  AND status IS DISTINCT FROM CASE
  WHEN NULLIF(BTRIM(code), '') IS NULL AND NULLIF(BTRIM(label), '') IS NULL THEN 'uncoded & unlabeled'
  WHEN NULLIF(BTRIM(code), '') IS NULL THEN 'uncoded'
  WHEN NULLIF(BTRIM(label), '') IS NULL THEN 'unlabeled'
  ELSE 'coded'
END;

CREATE TABLE IF NOT EXISTS audit_logs (
  id SERIAL PRIMARY KEY,
  record_id INTEGER REFERENCES records(id) ON DELETE CASCADE,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  column_name VARCHAR(100) NOT NULL,
  old_value TEXT,
  new_value TEXT,
  action VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS audit_logs_record_column_idx
ON audit_logs (record_id, column_name, created_at DESC, id DESC);

CREATE INDEX IF NOT EXISTS audit_logs_record_created_idx
ON audit_logs (record_id, created_at DESC, id DESC);

INSERT INTO users (name, email, role)
VALUES
  ('Adrian', 'adrian@example.com', 'coder'),
  ('Maria', 'maria@example.com', 'reviewer'),
  ('Elena', 'elena@example.com', 'coder')
ON CONFLICT (email) DO NOTHING;

INSERT INTO projects (name, source_file, file_hash)
SELECT source_file, source_file, MD5('legacy:' || source_file)
FROM records
WHERE source_file IS NOT NULL AND question_key IS NOT NULL
GROUP BY source_file
ON CONFLICT (file_hash) DO NOTHING;

UPDATE projects
SET name = REGEXP_REPLACE(source_file, '\.(csv|xlsx?)$', '', 'i')
WHERE name = source_file;

UPDATE records r
SET project_id = p.id
FROM projects p
WHERE r.project_id IS NULL
  AND r.source_file = p.source_file
  AND p.file_hash = MD5('legacy:' || r.source_file);

INSERT INTO projects (name, source_file, file_hash)
VALUES ('Demo project', 'demo', MD5('legacy:demo'))
ON CONFLICT (file_hash) DO NOTHING;

UPDATE records r
SET
  project_id = p.id,
  source_file = COALESCE(r.source_file, 'demo'),
  respondent_serial = COALESCE(r.respondent_serial, 'demo-' || LPAD(r.id::text, 3, '0')),
  respondent_id = COALESCE(r.respondent_id, 'demo-' || LPAD(r.id::text, 3, '0')),
  question_key = COALESCE(r.question_key, 'demo.question')
FROM projects p
WHERE r.project_id IS NULL
  AND p.file_hash = MD5('legacy:demo');

INSERT INTO studies (project_id, name, source_file, file_hash, created_by)
SELECT
  p.id,
  p.name,
  COALESCE(p.source_file, 'legacy-' || p.id),
  COALESCE(p.file_hash, MD5('legacy-project:' || p.id::text)),
  p.created_by
FROM projects p
WHERE p.source_file IS NOT NULL
ON CONFLICT (project_id, file_hash) DO NOTHING;

UPDATE records r
SET study_id = s.id
FROM studies s
WHERE r.study_id IS NULL
  AND s.project_id = r.project_id
  AND s.id = (
    SELECT MIN(s2.id)
    FROM studies s2
    WHERE s2.project_id = r.project_id
  );

INSERT INTO records (project_id, study_id, source_file, respondent_serial, respondent_id, question_key, response_text, label, code, status, assigned_user_id)
SELECT p.id, s.id, seed.*
FROM (
  VALUES
    ('demo', 'demo-001', 'demo-001', 'demo.question', 'Customer asked for refund after delayed shipment.', NULL, NULL, 'uncoded & unlabeled', 1),
    ('demo', 'demo-002', 'demo-002', 'demo.question', 'User reports login issue on mobile browser.', NULL, NULL, 'uncoded & unlabeled', 2),
    ('demo', 'demo-003', 'demo-003', 'demo.question', 'Survey response mentions fast checkout flow.', NULL, NULL, 'uncoded & unlabeled', 3),
    ('demo', 'demo-004', 'demo-004', 'demo.question', 'Client wants dark mode added to dashboard.', NULL, NULL, 'uncoded & unlabeled', 1),
    ('demo', 'demo-005', 'demo-005', 'demo.question', 'Bug report about export CSV missing headers.', NULL, NULL, 'uncoded & unlabeled', 2),
    ('demo', 'demo-006', 'demo-006', 'demo.question', 'Interview transcript references onboarding confusion.', NULL, NULL, 'uncoded & unlabeled', 3),
    ('demo', 'demo-007', 'demo-007', 'demo.question', 'Feedback praises quick support response.', NULL, NULL, 'uncoded & unlabeled', 1),
    ('demo', 'demo-008', 'demo-008', 'demo.question', 'Complaint about duplicate invoice email.', NULL, NULL, 'uncoded & unlabeled', 2),
    ('demo', 'demo-009', 'demo-009', 'demo.question', 'Suggestion to add keyboard shortcuts.', NULL, NULL, 'uncoded & unlabeled', 3),
    ('demo', 'demo-010', 'demo-010', 'demo.question', 'User requests easier password reset flow.', NULL, NULL, 'uncoded & unlabeled', 1),
    ('demo', 'demo-011', 'demo-011', 'demo.question', 'Comment about lag when loading reports.', NULL, NULL, 'uncoded & unlabeled', 2),
    ('demo', 'demo-012', 'demo-012', 'demo.question', 'Support note says issue resolved after cache clear.', NULL, NULL, 'uncoded & unlabeled', 3),
    ('demo', 'demo-013', 'demo-013', 'demo.question', 'Research note highlights need for better filters.', NULL, NULL, 'uncoded & unlabeled', 1),
    ('demo', 'demo-014', 'demo-014', 'demo.question', 'QA found typo in billing confirmation email.', NULL, NULL, 'uncoded & unlabeled', 2),
    ('demo', 'demo-015', 'demo-015', 'demo.question', 'Customer mentions helpful search suggestions.', NULL, NULL, 'uncoded & unlabeled', 3)
) AS seed(source_file, respondent_serial, respondent_id, question_key, response_text, label, code, status, assigned_user_id)
CROSS JOIN projects p
JOIN studies s ON s.project_id = p.id
WHERE p.file_hash = MD5('legacy:demo')
  AND NOT EXISTS (SELECT 1 FROM records LIMIT 1);

INSERT INTO codeframe_entries (project_id, code, label)
SELECT DISTINCT ON (r.project_id, BTRIM(codes.value))
  r.project_id,
  BTRIM(codes.value),
  BTRIM(labels.value)
FROM records r
JOIN LATERAL regexp_split_to_table(r.code, '[[:space:]]*,[[:space:]]*') WITH ORDINALITY codes(value, position) ON TRUE
JOIN LATERAL regexp_split_to_table(r.label, '[[:space:]]*,[[:space:]]*') WITH ORDINALITY labels(value, position)
  ON labels.position = codes.position
WHERE r.project_id IS NOT NULL
  AND NULLIF(BTRIM(codes.value), '') IS NOT NULL
  AND NULLIF(BTRIM(labels.value), '') IS NOT NULL
ORDER BY r.project_id, BTRIM(codes.value), r.updated_at DESC, r.id DESC
ON CONFLICT (project_id, code)
DO UPDATE SET label = EXCLUDED.label, updated_at = NOW()
WHERE codeframe_entries.source_type = 'manual';