-- Мэдэгдэл, Шаардлагын Систем 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 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)
);

-- Сүүлд үүсгэсэн таблица
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 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);
