-- sql/pothole_reporting.sql
-- Create database + tables for the Dial-A-Kgerekgere Pothole Reporting App

CREATE DATABASE IF NOT EXISTS pothole_reporting
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE pothole_reporting;

CREATE TABLE IF NOT EXISTS pothole_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  photo_blob LONGBLOB NOT NULL,
  photo_mime VARCHAR(120) NOT NULL,
  photo_filename VARCHAR(255) NULL,

  latitude VARCHAR(32) NOT NULL,
  longitude VARCHAR(32) NOT NULL,

  municipality VARCHAR(120) NOT NULL,
  town VARCHAR(120) NOT NULL,
  village VARCHAR(120) NULL,
  street VARCHAR(180) NULL,

  description TEXT NULL,

  status ENUM('OPEN','IN_PROGRESS','CLOSED') NOT NULL DEFAULT 'OPEN',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,

  PRIMARY KEY (id),
  KEY idx_location (municipality, town, village),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;