CREATE DATABASE IF NOT EXISTS nakhsha_customer_central CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE nakhsha_customer_central;

CREATE TABLE roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_key VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

INSERT INTO roles(role_key,name) VALUES
('admin','Administrator'),
('customer','Customer'),
('site_engineer','Site Engineer'),
('project_manager','Project Manager'),
('department_user','Department User')
ON DUPLICATE KEY UPDATE name=VALUES(name);

CREATE TABLE departments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(40) NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,
  designation VARCHAR(120) NULL,
  avatar_url VARCHAR(500) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_role FOREIGN KEY(role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE user_departments (
  user_id BIGINT UNSIGNED NOT NULL,
  department_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(user_id, department_id),
  CONSTRAINT fk_ud_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ud_department FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE sites (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  site_code VARCHAR(60) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  address TEXT NULL,
  city VARCHAR(100) NULL DEFAULT 'Mysuru',
  customer_id BIGINT UNSIGNED NOT NULL,
  project_manager_id BIGINT UNSIGNED NULL,
  site_engineer_id BIGINT UNSIGNED NULL,
  status ENUM('planning','active','on_hold','completed','handed_over') NOT NULL DEFAULT 'active',
  start_date DATE NULL,
  expected_completion_date DATE NULL,
  progress_percent TINYINT UNSIGNED NOT NULL DEFAULT 0,
  cover_image_url VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_site_customer FOREIGN KEY(customer_id) REFERENCES users(id),
  CONSTRAINT fk_site_pm FOREIGN KEY(project_manager_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_site_se FOREIGN KEY(site_engineer_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE site_team (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  site_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  designation VARCHAR(120) NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_site_team(site_id,user_id),
  CONSTRAINT fk_st_site FOREIGN KEY(site_id) REFERENCES sites(id) ON DELETE CASCADE,
  CONSTRAINT fk_st_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE issue_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(140) NOT NULL,
  department_id INT UNSIGNED NULL,
  default_priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_issue_dept FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE tickets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_no VARCHAR(40) NOT NULL UNIQUE,
  site_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  subject VARCHAR(180) NOT NULL,
  note TEXT NULL,
  priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  status ENUM('raised','pending','processing','approved','on_hold','resolved','closed','rejected') NOT NULL DEFAULT 'raised',
  assigned_to BIGINT UNSIGNED NULL,
  assigned_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  closed_at DATETIME NULL,
  INDEX idx_ticket_site_status(site_id,status),
  INDEX idx_ticket_assignee_status(assigned_to,status),
  CONSTRAINT fk_ticket_site FOREIGN KEY(site_id) REFERENCES sites(id),
  CONSTRAINT fk_ticket_customer FOREIGN KEY(customer_id) REFERENCES users(id),
  CONSTRAINT fk_ticket_category FOREIGN KEY(category_id) REFERENCES issue_categories(id),
  CONSTRAINT fk_ticket_assignee FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_ticket_assigner FOREIGN KEY(assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE ticket_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(80) NOT NULL,
  from_status VARCHAR(40) NULL,
  to_status VARCHAR(40) NULL,
  from_assignee BIGINT UNSIGNED NULL,
  to_assignee BIGINT UNSIGNED NULL,
  comment TEXT NULL,
  performed_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_th_ticket FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
  CONSTRAINT fk_th_from_assignee FOREIGN KEY(from_assignee) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_th_to_assignee FOREIGN KEY(to_assignee) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_th_actor FOREIGN KEY(performed_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE feedback (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  site_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  rating TINYINT UNSIGNED NULL,
  category VARCHAR(100) NULL,
  message TEXT NOT NULL,
  status ENUM('new','reviewing','acknowledged','closed') NOT NULL DEFAULT 'new',
  assigned_to BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_feedback_site FOREIGN KEY(site_id) REFERENCES sites(id),
  CONSTRAINT fk_feedback_customer FOREIGN KEY(customer_id) REFERENCES users(id),
  CONSTRAINT fk_feedback_assignee FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE device_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token VARCHAR(500) NOT NULL,
  platform ENUM('android','ios','web','unknown') NOT NULL DEFAULT 'unknown',
  last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_device_token(token(191)),
  CONSTRAINT fk_device_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  body VARCHAR(500) NOT NULL,
  type VARCHAR(60) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  push_state ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued',
  push_error VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  read_at DATETIME NULL,
  CONSTRAINT fk_notification_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE api_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_token_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO issue_categories(name,default_priority) VALUES
('Site execution concern','normal'),
('Electrical','normal'),
('Plumbing','high'),
('Finishing / quality','normal'),
('Safety concern','urgent'),
('Schedule / delay','high'),
('Other','normal');

CREATE TABLE ticket_attachments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  uploaded_by BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  storage_path VARCHAR(600) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ticket_attachment_ticket(ticket_id),
  CONSTRAINT fk_ta_ticket FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
  CONSTRAINT fk_ta_user FOREIGN KEY(uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB;