

CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  owner_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;

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,
  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),
  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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS measurements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  date DATE NOT NULL,
  qty DECIMAL(11,3) NOT NULL,
  note TEXT NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_meas_item (item_id),
  INDEX idx_meas_date (date),
  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;

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_meas 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,
  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;

-- Hinweis: Der Hash oben ist für "admin123"
