-- Мэдэгдэл, Шаардлагын Систем Database

CREATE DATABASE IF NOT EXISTS police_notification_db;
USE police_notification_db;

-- Дүүрэг таблица
CREATE TABLE IF NOT EXISTS districts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Хороо таблица
CREATE TABLE IF NOT EXISTS khoroos (
  id INT PRIMARY KEY AUTO_INCREMENT,
  district_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE CASCADE,
  UNIQUE KEY unique_khoroo (district_id, name)
);

-- Хэрэглэгч таблица
CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(20),
  email_verified BOOLEAN DEFAULT FALSE,
  role ENUM('admin', 'officer', 'user') DEFAULT 'user',
  user_type ENUM('citizen', 'organization', 'police') DEFAULT 'citizen',
  organization_name VARCHAR(255),
  civic_id VARCHAR(50),
  address TEXT,
  signage_name VARCHAR(255),
  coordinates VARCHAR(50),
  district_id INT,
  khoroo_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL,
  FOREIGN KEY (khoroo_id) REFERENCES khoroos(id) ON DELETE SET NULL
);

-- Мэдэгдэл таблица
CREATE TABLE IF NOT EXISTS notifications (
  id INT PRIMARY KEY AUTO_INCREMENT,
  document_number VARCHAR(50) UNIQUE NOT NULL,
  sent_by INT NOT NULL,
  recipient_id INT,
  type VARCHAR(100) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  due_date DATE NOT NULL,
  status ENUM('pending', 'read', 'responded', 'expired') DEFAULT 'pending',
  response TEXT,
  response_date DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (sent_by) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_document_number (document_number),
  INDEX idx_sent_by (sent_by),
  INDEX idx_recipient_id (recipient_id),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
);

-- Статистик таблица
CREATE TABLE IF NOT EXISTS statistics (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  total_sent INT DEFAULT 0,
  total_pending INT DEFAULT 0,
  total_responded INT DEFAULT 0,
  total_expired INT DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user (user_id)
);

-- Түүх таблица
CREATE TABLE IF NOT EXISTS notification_history (
  id INT PRIMARY KEY AUTO_INCREMENT,
  notification_id INT NOT NULL,
  status ENUM('pending', 'read', 'responded', 'expired'),
  changed_by INT,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  notes TEXT,
  FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
  FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_notification_id (notification_id),
  INDEX idx_changed_at (changed_at)
);

-- Анхны хэрэглэгч нэмэх (Администратор)
INSERT INTO users (username, password, first_name, last_name, role) VALUES
('admin', '$2a$10$Gu.IZ3U8c.uaL9L/xNp7.uYbhLg.d3cD8VKJqvZqkYKNTEWvQN4Ry', 'Админ', 'Хэрэглэгч', 'admin'),
('police_officer_01', '$2a$10$Gu.IZ3U8c.uaL9L/xNp7.uYbhLg.d3cD8VKJqvZqkYKNTEWvQN4Ry', 'Бат', 'Эрдэнэ', 'officer');

-- Сүүлд үүсгэсэн таблица
CREATE TABLE IF NOT EXISTS attachments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  notification_id INT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_type VARCHAR(50),
  file_size INT,
  uploaded_by INT,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS registration_requests (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  user_type ENUM('citizen', 'organization') NOT NULL,
  organization_name VARCHAR(255),
  civic_id VARCHAR(50),
  address TEXT,
  signage_name VARCHAR(255),
  coordinates VARCHAR(50),
  phone VARCHAR(20),
  district_id INT,
  khoroo_id INT,
  token VARCHAR(255) UNIQUE,
  expires_at DATETIME,
  verified BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL,
  FOREIGN KEY (khoroo_id) REFERENCES khoroos(id) ON DELETE SET NULL,
  INDEX idx_verified (verified),
  INDEX idx_expires_at (expires_at)
);

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  token VARCHAR(255) UNIQUE NOT NULL,
  expires_at DATETIME NOT NULL,
  used BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_token (token),
  INDEX idx_expires_at (expires_at)
);

-- Систем тохиргоо таблица
CREATE TABLE IF NOT EXISTS system_settings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  key_name VARCHAR(255) UNIQUE NOT NULL,
  value LONGTEXT,
  description TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Анхны тохиргоо утгууд оруулах
INSERT INTO system_settings (key_name, value, description) VALUES
('system_name', 'Цагдаагийн Мэдэгдэл Систем', 'Системийн нэр'),
('system_email', '', 'Емэйлийн хаяг'),
('smtp_host', '', 'SMTP серверийн хаяг'),
('smtp_port', '587', 'SMTP портын дугаар'),
('smtp_user', '', 'SMTP хэрэглэгчийн нэр'),
('smtp_password', '', 'SMTP нууц үг'),
('email_from', '', 'Емэйл илгээх хаяг'),
('require_email_verification', '1', 'Емэйл баталгаажуулалт шаардлагатай'),
('registration_auto_approve', '0', 'Бүртгэл автоматаар зөвшөөрөх'),
('enable_donation_popup', '1', 'Донэйшн popup цонх харуулах'),
('enable_notifications', '1', 'Мэдэгдлүүдийг идэвхжүүлэх')
ON DUPLICATE KEY UPDATE value=VALUES(value);