CREATE DATABASE IF NOT EXISTS elif_erp_crm
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_turkish_ci;

USE elif_erp_crm;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module VARCHAR(100) NOT NULL,
  action VARCHAR(100) NOT NULL,
  slug VARCHAR(150) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_permissions_module_action (module, action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE role_permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_role_permission (role_id, permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(30) NULL,
  profile_photo VARCHAR(255) NULL,
  status ENUM('active','passive','suspended') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  remember_token VARCHAR(100) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  INDEX idx_users_role_id (role_id),
  INDEX idx_users_status (status),
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE user_module_permissions (
  user_id BIGINT UNSIGNED NOT NULL,
  module_key VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, module_key),
  INDEX idx_user_module_permissions_module (module_key),
  CONSTRAINT fk_user_module_permissions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE staff_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  banner_count INT UNSIGNED NOT NULL DEFAULT 0,
  commission_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  employment_status ENUM('active','on_leave','left') NOT NULL DEFAULT 'active',
  started_at DATE NULL,
  note TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_staff_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE cities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  plate_code VARCHAR(10) NULL,
  UNIQUE KEY uq_cities_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE districts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  city_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  UNIQUE KEY uq_district_city_name (city_id, name),
  CONSTRAINT fk_districts_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE neighborhoods (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  district_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  UNIQUE KEY uq_neighborhood_district_name (district_id, name),
  CONSTRAINT fk_neighborhoods_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE portfolios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_no VARCHAR(50) NOT NULL UNIQUE,
  consultant_id BIGINT UNSIGNED NOT NULL,
  type ENUM('sale','rent') NOT NULL,
  category VARCHAR(100) NOT NULL,
  city_id BIGINT UNSIGNED NULL,
  district_id BIGINT UNSIGNED NULL,
  neighborhood_id BIGINT UNSIGNED NULL,
  address TEXT NULL,
  block_no VARCHAR(50) NULL,
  parcel_no VARCHAR(50) NULL,
  map_url VARCHAR(500) NULL,
  owner_name VARCHAR(150) NULL,
  owner_phone VARCHAR(30) NULL,
  owner_email VARCHAR(190) NULL,
  owner_address TEXT NULL,
  price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'TRY',
  status ENUM('draft','active','sold','rented','archived') NOT NULL DEFAULT 'draft',
  description TEXT NULL,
  published_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  INDEX idx_portfolios_consultant (consultant_id),
  INDEX idx_portfolios_status (status),
  INDEX idx_portfolios_location (city_id, district_id, neighborhood_id),
  INDEX idx_portfolios_type_category (type, category),
  INDEX idx_portfolios_price (price),
  CONSTRAINT fk_portfolios_consultant FOREIGN KEY (consultant_id) REFERENCES users(id),
  CONSTRAINT fk_portfolios_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL,
  CONSTRAINT fk_portfolios_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL,
  CONSTRAINT fk_portfolios_neighborhood FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE portfolio_photos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  is_cover TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_portfolio_photos_portfolio (portfolio_id),
  CONSTRAINT fk_portfolio_photos_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE portfolio_workflows (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  step ENUM('received','photo_taken','banner_installed','published','sold','rented') NOT NULL,
  status ENUM('pending','completed','skipped') NOT NULL DEFAULT 'pending',
  completed_by BIGINT UNSIGNED NULL,
  completed_at DATETIME NULL,
  note TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_portfolio_workflow_step (portfolio_id, step),
  INDEX idx_portfolio_workflows_status (status),
  CONSTRAINT fk_portfolio_workflows_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id) ON DELETE CASCADE,
  CONSTRAINT fk_portfolio_workflows_user FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  assigned_user_id BIGINT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  source VARCHAR(100) NULL,
  status ENUM('active','passive','blacklist') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  UNIQUE KEY uq_customers_phone (phone),
  INDEX idx_customers_email (email),
  INDEX idx_customers_assigned_user (assigned_user_id),
  INDEX idx_customers_status (status),
  CONSTRAINT fk_customers_assigned_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE customer_demands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  type ENUM('sale','rent') NOT NULL,
  category VARCHAR(100) NOT NULL,
  city_id BIGINT UNSIGNED NULL,
  district_id BIGINT UNSIGNED NULL,
  neighborhood_id BIGINT UNSIGNED NULL,
  min_price DECIMAL(15,2) NULL,
  max_price DECIMAL(15,2) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'TRY',
  room_count VARCHAR(30) NULL,
  min_area INT UNSIGNED NULL,
  max_area INT UNSIGNED NULL,
  status ENUM('open','matched','closed','cancelled') NOT NULL DEFAULT 'open',
  description TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_customer_demands_customer (customer_id),
  INDEX idx_customer_demands_assigned_user (assigned_user_id),
  INDEX idx_customer_demands_status (status),
  INDEX idx_customer_demands_search (type, category, city_id, district_id, neighborhood_id),
  INDEX idx_customer_demands_price (min_price, max_price),
  CONSTRAINT fk_customer_demands_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_customer_demands_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_customer_demands_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL,
  CONSTRAINT fk_customer_demands_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL,
  CONSTRAINT fk_customer_demands_neighborhood FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE customer_portfolio_matches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_demand_id BIGINT UNSIGNED NOT NULL,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  match_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  status ENUM('suggested','contacted','rejected','accepted') NOT NULL DEFAULT 'suggested',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_demand_portfolio_match (customer_demand_id, portfolio_id),
  INDEX idx_customer_portfolio_matches_portfolio (portfolio_id),
  INDEX idx_customer_portfolio_matches_status (status),
  CONSTRAINT fk_matches_demand FOREIGN KEY (customer_demand_id) REFERENCES customer_demands(id) ON DELETE CASCADE,
  CONSTRAINT fk_matches_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE appointments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NULL,
  portfolio_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  appointment_type ENUM('showing','meeting','call','follow_up') NOT NULL DEFAULT 'meeting',
  start_at DATETIME NOT NULL,
  end_at DATETIME NULL,
  status ENUM('planned','completed','cancelled','missed') NOT NULL DEFAULT 'planned',
  external_calendar_provider VARCHAR(50) NULL,
  external_calendar_event_id VARCHAR(190) NULL,
  note TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_appointments_customer (customer_id),
  INDEX idx_appointments_portfolio (portfolio_id),
  INDEX idx_appointments_user_start (user_id, start_at),
  INDEX idx_appointments_status (status),
  INDEX idx_appointments_start (start_at),
  INDEX idx_appointments_status_start (status, start_at),
  INDEX idx_appointments_type_start (appointment_type, start_at),
  CONSTRAINT fk_appointments_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_appointments_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id) ON DELETE SET NULL,
  CONSTRAINT fk_appointments_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE portfolio_activities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NULL,
  consultant_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  activity_type ENUM('showing','call','note','offer','status_change') NOT NULL,
  result VARCHAR(255) NULL,
  activity_date DATETIME NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_portfolio_activities_portfolio_date (portfolio_id, activity_date),
  INDEX idx_portfolio_activities_customer (customer_id),
  INDEX idx_portfolio_activities_consultant (consultant_id),
  CONSTRAINT fk_portfolio_activities_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id) ON DELETE CASCADE,
  CONSTRAINT fk_portfolio_activities_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_portfolio_activities_consultant FOREIGN KEY (consultant_id) REFERENCES users(id),
  CONSTRAINT fk_portfolio_activities_appointment FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE lead_pool_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  assigned_user_id BIGINT UNSIGNED NULL,
  region VARCHAR(150) NOT NULL,
  address TEXT NULL,
  status ENUM('waiting','called','interested','not_interested','converted') NOT NULL DEFAULT 'waiting',
  last_call_at DATETIME NULL,
  note TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_lead_pool_assigned_user (assigned_user_id),
  INDEX idx_lead_pool_status (status),
  INDEX idx_lead_pool_last_call (last_call_at),
  CONSTRAINT fk_lead_pool_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE sales_rentals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NULL,
  consultant_id BIGINT UNSIGNED NOT NULL,
  transaction_type ENUM('sold','rented') NOT NULL,
  final_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  commission_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  closed_at DATETIME NOT NULL,
  note TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sales_rentals_portfolio (portfolio_id),
  INDEX idx_sales_rentals_customer (customer_id),
  INDEX idx_sales_rentals_consultant (consultant_id),
  INDEX idx_sales_rentals_closed_at (closed_at),
  CONSTRAINT fk_sales_rentals_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id),
  CONSTRAINT fk_sales_rentals_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_sales_rentals_consultant FOREIGN KEY (consultant_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE notes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  notable_type VARCHAR(80) NOT NULL,
  notable_id BIGINT UNSIGNED NOT NULL,
  note TEXT NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_notes_notable (notable_type, notable_id),
  INDEX idx_notes_user (user_id),
  CONSTRAINT fk_notes_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE chat_conversations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type ENUM('private','group') NOT NULL DEFAULT 'private',
  title VARCHAR(150) NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_chat_conversations_created_by (created_by),
  CONSTRAINT fk_chat_conversations_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE chat_participants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  last_read_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_chat_participant (conversation_id, user_id),
  INDEX idx_chat_participants_user (user_id),
  CONSTRAINT fk_chat_participants_conversation FOREIGN KEY (conversation_id) REFERENCES chat_conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_chat_participants_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE chat_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender_id BIGINT UNSIGNED NOT NULL,
  message TEXT NULL,
  attachment_path VARCHAR(255) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  INDEX idx_chat_messages_conversation_created (conversation_id, created_at),
  INDEX idx_chat_messages_sender (sender_id),
  CONSTRAINT fk_chat_messages_conversation FOREIGN KEY (conversation_id) REFERENCES chat_conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_chat_messages_sender FOREIGN KEY (sender_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(100) NOT NULL,
  title VARCHAR(180) NOT NULL,
  message TEXT NULL,
  data JSON NULL,
  read_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_notifications_user_read (user_id, read_at),
  INDEX idx_notifications_created_at (created_at),
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE files (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uploaded_by BIGINT UNSIGNED NULL,
  fileable_type VARCHAR(80) NULL,
  fileable_id BIGINT UNSIGNED NULL,
  original_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_files_fileable (fileable_type, fileable_id),
  INDEX idx_files_uploaded_by (uploaded_by),
  CONSTRAINT fk_files_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  report_type VARCHAR(100) NOT NULL,
  file_path VARCHAR(255) NULL,
  filters JSON NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_reports_user (user_id),
  INDEX idx_reports_type_created (report_type, created_at),
  CONSTRAINT fk_reports_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(150) NOT NULL UNIQUE,
  `value` JSON NULL,
  `group` VARCHAR(100) NOT NULL DEFAULT 'general',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_settings_group (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(100) NULL,
  entity_id BIGINT UNSIGNED NULL,
  old_values JSON NULL,
  new_values JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_logs_user (user_id),
  INDEX idx_audit_logs_entity (entity_type, entity_id),
  INDEX idx_audit_logs_action_created (action, created_at),
  INDEX idx_audit_logs_created (created_at),
  INDEX idx_audit_logs_ip_created (ip_address, created_at),
  INDEX idx_audit_logs_module_created (entity_type, created_at),
  CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE user_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  session_token_hash VARCHAR(255) NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_sessions_token (session_token_hash),
  INDEX idx_user_sessions_user (user_id),
  INDEX idx_user_sessions_expires (expires_at),
  CONSTRAINT fk_user_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO roles (id, name, slug, description) VALUES
  (1, 'Yonetici', 'admin', 'Tam yetkili sistem yoneticisi'),
  (2, 'Personel', 'staff', 'Yetkilendirildigi modulleri kullanabilen personel')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  description = VALUES(description);

INSERT INTO permissions (module, action, slug, description) VALUES
  ('dashboard', 'view', 'dashboard.view', 'Dashboard goruntuleme'),
  ('portfolios', 'view', 'portfolios.view', 'Portfoy listeleme ve goruntuleme'),
  ('portfolios', 'create', 'portfolios.create', 'Portfoy olusturma'),
  ('portfolios', 'edit', 'portfolios.edit', 'Portfoy duzenleme'),
  ('portfolios', 'delete', 'portfolios.delete', 'Portfoy arsivleme veya silme'),
  ('customers', 'view', 'customers.view', 'Musteri goruntuleme'),
  ('customers', 'create', 'customers.create', 'Musteri olusturma'),
  ('customers', 'edit', 'customers.edit', 'Musteri duzenleme'),
  ('demands', 'view', 'demands.view', 'Musteri talebi goruntuleme'),
  ('demands', 'create', 'demands.create', 'Musteri talebi olusturma'),
  ('appointments', 'view', 'appointments.view', 'Randevu goruntuleme'),
  ('appointments', 'create', 'appointments.create', 'Randevu olusturma'),
  ('staff', 'manage', 'staff.manage', 'Personel yonetimi'),
  ('chat', 'use', 'chat.use', 'Kurumsal chat kullanimi'),
  ('reports', 'view', 'reports.view', 'Rapor goruntuleme'),
  ('reports', 'export', 'reports.export', 'PDF ve Excel rapor alma'),
  ('settings', 'manage', 'settings.manage', 'Sistem ayarlari yonetimi')
ON DUPLICATE KEY UPDATE
  module = VALUES(module),
  action = VALUES(action),
  description = VALUES(description);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions
WHERE slug IN (
  'dashboard.view',
  'portfolios.view',
  'portfolios.create',
  'portfolios.edit',
  'customers.view',
  'customers.create',
  'customers.edit',
  'demands.view',
  'demands.create',
  'appointments.view',
  'appointments.create',
  'chat.use'
);

INSERT INTO users (
  id,
  role_id,
  name,
  email,
  password,
  phone,
  status
) VALUES (
  1,
  1,
  'Sistem Yoneticisi',
  'admin@elifgayrimenkul.local',
  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi',
  NULL,
  'active'
)
ON DUPLICATE KEY UPDATE
  role_id = VALUES(role_id),
  name = VALUES(name),
  password = VALUES(password),
  status = VALUES(status);

INSERT IGNORE INTO user_module_permissions (user_id, module_key)
SELECT users.id, modules.module_key
FROM users
CROSS JOIN (
  SELECT 'dashboard' AS module_key UNION ALL
  SELECT 'portfolios' UNION ALL
  SELECT 'crm' UNION ALL
  SELECT 'demands' UNION ALL
  SELECT 'staff' UNION ALL
  SELECT 'reports' UNION ALL
  SELECT 'logs' UNION ALL
  SELECT 'settings'
) AS modules
WHERE users.deleted_at IS NULL;