

CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  project_number VARCHAR(64) NOT NULL,
  planner_email VARCHAR(255) NULL,
  owner_user_id INT NULL,
  bauleiter_user_id INT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_projects_owner (owner_user_id),
  CONSTRAINT fk_projects_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Project access (admins see all; users see only assigned/imported projects)
CREATE TABLE IF NOT EXISTS project_access (
  project_id INT NOT NULL,
  user_id INT NOT NULL,
  access_role VARCHAR(20) NOT NULL DEFAULT 'owner',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (project_id, user_id),
  INDEX idx_pa_user (user_id),
  INDEX idx_pa_project (project_id),
  CONSTRAINT fk_pa_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_settings (
  k VARCHAR(128) PRIMARY KEY,
  v LONGTEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lv_imports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  filename VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_lv_imports_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lv_nodes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  import_id INT NOT NULL,
  node_id INT NULL,
  parent_id INT NULL,
  rno_part VARCHAR(32) NULL,
  label VARCHAR(500) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_nodes_project (project_id),
  INDEX idx_nodes_parent (parent_id),
  CONSTRAINT fk_nodes_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_nodes_import FOREIGN KEY (import_id) REFERENCES lv_imports(id) ON DELETE CASCADE,
  CONSTRAINT fk_nodes_parent FOREIGN KEY (parent_id) REFERENCES lv_nodes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lv_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  import_id INT NOT NULL,
  node_id INT NULL,
  parent_item_id INT NULL,
  is_extra TINYINT(1) NOT NULL DEFAULT 0,
  oz VARCHAR(64) NULL,
  short_text TEXT NULL,
  long_text MEDIUMTEXT NULL,
  unit VARCHAR(16) NULL,
  lv_qty DECIMAL(11,3) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_lv_items_project (project_id),
  INDEX idx_lv_items_oz (oz),
  INDEX idx_lv_items_parent (parent_item_id),
  FULLTEXT INDEX ftx_lv_text (short_text, long_text),
  CONSTRAINT fk_lv_items_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_lv_items_import FOREIGN KEY (import_id) REFERENCES lv_imports(id) ON DELETE CASCADE,
  CONSTRAINT fk_lv_items_node FOREIGN KEY (node_id) REFERENCES lv_nodes(id) ON DELETE SET NULL,
  CONSTRAINT fk_lv_items_parent FOREIGN KEY (parent_item_id) REFERENCES lv_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS measurements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  client_uuid CHAR(36) NULL,
  date DATE NOT NULL,
  qty DECIMAL(11,3) NOT NULL,
  calc_expr VARCHAR(1000) NULL,
  note TEXT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'final',
  deleted_at DATETIME NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_meas_item (item_id),
  INDEX idx_meas_date (date),
  UNIQUE KEY uq_meas_client_uuid (client_uuid),
  CONSTRAINT fk_meas_item FOREIGN KEY (item_id) REFERENCES lv_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_meas_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Files/photos attached to measurements
CREATE TABLE IF NOT EXISTS measurement_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  measurement_id INT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime VARCHAR(64) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_ph_meas (measurement_id),
  CONSTRAINT fk_ph_meas FOREIGN KEY (measurement_id) REFERENCES measurements(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Backwards compat (older installs)
CREATE TABLE IF NOT EXISTS measurement_photos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  measurement_id INT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_ph_meas (measurement_id),
  CONSTRAINT fk_ph_meas2 FOREIGN KEY (measurement_id) REFERENCES measurements(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS login_attempts (
  ip VARCHAR(64) PRIMARY KEY,
  fail_count INT NOT NULL DEFAULT 0,
  first_fail_at DATETIME NULL,
  last_fail_at DATETIME NULL,
  banned_until DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ts_utc DATETIME NOT NULL,
  user_id INT NULL,
  username VARCHAR(190) NULL,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,
  role VARCHAR(32) NOT NULL DEFAULT 'bauleiter',
  ip VARCHAR(64) NOT NULL,
  user_agent VARCHAR(255) NOT NULL,
  request_id VARCHAR(64) NULL,
  action VARCHAR(64) NOT NULL,
  object_type VARCHAR(32) NULL,
  object_id VARCHAR(64) NULL,
  project_id INT NULL,
  item_id INT NULL,
  oz VARCHAR(64) NULL,
  before_json LONGTEXT NULL,
  after_json LONGTEXT NULL,
  meta_json LONGTEXT NULL,
  prev_hash VARCHAR(64) NULL,
  entry_hash VARCHAR(64) NULL,
  INDEX idx_audit_project_ts (project_id, ts_utc),
  INDEX idx_audit_item_ts (item_id, ts_utc),
  INDEX idx_audit_user_ts (user_id, ts_utc),
  INDEX idx_audit_action_ts (action, ts_utc),
  INDEX idx_audit_obj (object_type, object_id),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE projects ADD COLUMN IF NOT EXISTS last_reminder_sent_at DATETIME NULL;

-- Optional: Projektnummer
-- Für Bestandsdatenbanken wird die Spalte (falls fehlend) als NULLABLE ergänzt; die App erzwingt die Pflicht beim Speichern.
ALTER TABLE projects ADD COLUMN IF NOT EXISTS project_number VARCHAR(64) NULL;


-- Username+IP Login Attempts
CREATE TABLE IF NOT EXISTS login_attempts_user (
  ip VARCHAR(64) NOT NULL,
  email VARCHAR(190) NOT NULL,
  fail_count INT NOT NULL DEFAULT 0,
  first_fail_at DATETIME NULL,
  last_fail_at DATETIME NULL,
  banned_until DATETIME NULL,
  PRIMARY KEY (ip, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
