-- Migration: Admin & Feedback (2025-08-27)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120),
  email VARCHAR(200) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS feedback (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120),
  email VARCHAR(200),
  role VARCHAR(60),
  page VARCHAR(200),
  message TEXT NOT NULL,
  rating INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Optional: create countries/policies if they don't exist (minimal structure)
CREATE TABLE IF NOT EXISTS countries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) UNIQUE,
  iso2 CHAR(2),
  iso3 CHAR(3),
  region VARCHAR(120)
);

CREATE TABLE IF NOT EXISTS policies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  country_id INT,
  title VARCHAR(255) NOT NULL,
  category VARCHAR(120),
  year INT,
  link TEXT,
  summary TEXT,
  FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL
);
