CREATE TABLE IF NOT EXISTS projects (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'inactive',
  notes TEXT NULL,
  expected_interval_seconds INT NULL,
  night_start VARCHAR(5) NOT NULL DEFAULT '18:00',
  night_end VARCHAR(5) NOT NULL DEFAULT '06:00',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at DATETIME NULL,
  ended_at DATETIME NULL,
  KEY projects_status_idx (status),
  KEY projects_created_at_idx (created_at)
);

ALTER TABLE meter_samples
  ADD COLUMN IF NOT EXISTS project_id INT NULL AFTER id;

ALTER TABLE ingest_requests
  ADD COLUMN IF NOT EXISTS project_id INT NULL AFTER id;

INSERT INTO projects (
  name,
  status,
  notes,
  expected_interval_seconds,
  night_start,
  night_end,
  started_at
)
SELECT
  'Euromoulders',
  'active',
  'Initial PoC project created during project-aware dashboard migration.',
  60,
  '18:00',
  '06:00',
  CURRENT_TIMESTAMP
WHERE NOT EXISTS (
  SELECT 1 FROM projects WHERE name = 'Euromoulders'
);

UPDATE projects
SET status = 'inactive'
WHERE status = 'active'
  AND name <> 'Euromoulders'
  AND EXISTS (SELECT 1 FROM projects WHERE name = 'Euromoulders');

UPDATE meter_samples
SET project_id = (
  SELECT id
  FROM projects
  WHERE name = 'Euromoulders'
  ORDER BY id
  LIMIT 1
)
WHERE project_id IS NULL;

UPDATE ingest_requests
SET project_id = (
  SELECT id
  FROM projects
  WHERE name = 'Euromoulders'
  ORDER BY id
  LIMIT 1
)
WHERE project_id IS NULL;

ALTER TABLE meter_samples
  DROP INDEX IF EXISTS meter_samples_gateway_serial_sampled_at_uq;

CREATE UNIQUE INDEX IF NOT EXISTS meter_samples_project_gateway_serial_sampled_at_uq
  ON meter_samples (project_id, gateway_id, serial_number, sampled_at);

CREATE INDEX IF NOT EXISTS meter_samples_project_sampled_at_idx
  ON meter_samples (project_id, sampled_at);

CREATE INDEX IF NOT EXISTS ingest_requests_project_received_at_idx
  ON ingest_requests (project_id, received_at);