-- Sigorta Acente Yönetim Sistemi - MySQL 8+
-- Charset: utf8mb4

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS activity_logs;
DROP TABLE IF EXISTS message_logs;
DROP TABLE IF EXISTS customer_notes;
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS reminders;
DROP TABLE IF EXISTS cash_transactions;
DROP TABLE IF EXISTS damages;
DROP TABLE IF EXISTS cari_movements;
DROP TABLE IF EXISTS collections;
DROP TABLE IF EXISTS policies;
DROP TABLE IF EXISTS offers;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS tali;
DROP TABLE IF EXISTS customer_groups;
DROP TABLE IF EXISTS insurance_products;
DROP TABLE IF EXISTS branches;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  slug VARCHAR(40) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(64) NOT NULL UNIQUE,
  label VARCHAR(120) NOT NULL,
  category VARCHAR(60) NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE branches (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(20) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_branches_parent FOREIGN KEY (parent_id) REFERENCES branches(id) ON DELETE SET NULL,
  INDEX idx_branches_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  phone VARCHAR(40) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id),
  CONSTRAINT fk_users_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
  INDEX idx_users_role (role_id),
  INDEX idx_users_branch (branch_id),
  INDEX idx_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id INT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(512) NULL,
  meta_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_logs_user (user_id),
  INDEX idx_logs_entity (entity_type, entity_id),
  INDEX idx_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_groups (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_cg_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tali (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NULL,
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tali_name (name(80))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED NULL,
  tali_id INT UNSIGNED NULL,
  customer_type ENUM('bireysel','kurumsal') NOT NULL DEFAULT 'bireysel',
  name VARCHAR(200) NOT NULL,
  identity_no VARCHAR(32) NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  city VARCHAR(80) NULL,
  birth_or_foundation_date DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_customers_group FOREIGN KEY (group_id) REFERENCES customer_groups(id) ON DELETE SET NULL,
  CONSTRAINT fk_customers_tali FOREIGN KEY (tali_id) REFERENCES tali(id) ON DELETE SET NULL,
  INDEX idx_customers_group (group_id),
  INDEX idx_customers_tali (tali_id),
  INDEX idx_customers_name (name(80)),
  INDEX idx_customers_identity (identity_no),
  INDEX idx_customers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE insurance_products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  category VARCHAR(80) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_products_category (category),
  INDEX idx_products_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE offers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NULL,
  product_id INT UNSIGNED NOT NULL,
  status ENUM('taslak','gonderildi','kabul','red','policeye_dondu') NOT NULL DEFAULT 'taslak',
  premium DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  valid_until DATE NULL,
  converted_policy_id INT UNSIGNED NULL,
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_offers_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_offers_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
  CONSTRAINT fk_offers_product FOREIGN KEY (product_id) REFERENCES insurance_products(id),
  CONSTRAINT fk_offers_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_offers_customer (customer_id),
  INDEX idx_offers_status (status),
  INDEX idx_offers_created (created_at),
  INDEX idx_offers_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE policies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NULL,
  product_id INT UNSIGNED NOT NULL,
  offer_id INT UNSIGNED NULL,
  renewed_from_policy_id INT UNSIGNED NULL,
  transferred_from_policy_id INT UNSIGNED NULL,
  policy_no VARCHAR(64) NOT NULL,
  source ENUM('sistem','harici','transfer') NOT NULL DEFAULT 'sistem',
  status ENUM('taslak','aktif','tecil_bekliyor','iptal','sona_erdi') NOT NULL DEFAULT 'aktif',
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  premium DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  commission_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_policies_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_policies_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
  CONSTRAINT fk_policies_product FOREIGN KEY (product_id) REFERENCES insurance_products(id),
  CONSTRAINT fk_policies_offer FOREIGN KEY (offer_id) REFERENCES offers(id) ON DELETE SET NULL,
  CONSTRAINT fk_policies_renewed FOREIGN KEY (renewed_from_policy_id) REFERENCES policies(id) ON DELETE SET NULL,
  CONSTRAINT fk_policies_transferred FOREIGN KEY (transferred_from_policy_id) REFERENCES policies(id) ON DELETE SET NULL,
  CONSTRAINT fk_policies_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uk_policy_no (policy_no),
  INDEX idx_policies_customer (customer_id),
  INDEX idx_policies_dates (start_date, end_date),
  INDEX idx_policies_status (status),
  INDEX idx_policies_product (product_id),
  INDEX idx_policies_branch (branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE offers
  ADD CONSTRAINT fk_offers_converted_policy FOREIGN KEY (converted_policy_id) REFERENCES policies(id) ON DELETE SET NULL;

CREATE TABLE collections (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  policy_id INT UNSIGNED NULL,
  amount DECIMAL(14,2) NOT NULL,
  payment_method ENUM('kart','cek','senet','havale','nakit','diger') NOT NULL DEFAULT 'havale',
  due_date DATE NULL,
  paid_at DATETIME NULL,
  status ENUM('beklemede','tahsil_edildi','gecikti','iptal') NOT NULL DEFAULT 'beklemede',
  reference_no VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_coll_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_coll_policy FOREIGN KEY (policy_id) REFERENCES policies(id) ON DELETE SET NULL,
  CONSTRAINT fk_coll_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_coll_customer (customer_id),
  INDEX idx_coll_policy (policy_id),
  INDEX idx_coll_status (status),
  INDEX idx_coll_due (due_date),
  INDEX idx_coll_paid (paid_at),
  INDEX idx_coll_method (payment_method)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cari_movements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  movement_type ENUM('borc','alacak') NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  description VARCHAR(500) NULL,
  ref_type VARCHAR(40) NULL,
  ref_id INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cari_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  INDEX idx_cari_customer (customer_id),
  INDEX idx_cari_ref (ref_type, ref_id),
  INDEX idx_cari_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE damages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  policy_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  claim_no VARCHAR(64) NULL,
  status ENUM('bildirildi','incelemede','onaylandi','reddedildi','odendi') NOT NULL DEFAULT 'bildirildi',
  reported_amount DECIMAL(14,2) NULL,
  paid_amount DECIMAL(14,2) NULL,
  report_date DATE NOT NULL,
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_dam_pol FOREIGN KEY (policy_id) REFERENCES policies(id) ON DELETE CASCADE,
  CONSTRAINT fk_dam_cust FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_dam_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_dam_policy (policy_id),
  INDEX idx_dam_customer (customer_id),
  INDEX idx_dam_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cash_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NULL,
  transaction_type ENUM('gelir','gider') NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  category VARCHAR(80) NULL,
  description VARCHAR(500) NULL,
  transaction_date DATE NOT NULL,
  user_id INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cash_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
  CONSTRAINT fk_cash_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_cash_branch (branch_id),
  INDEX idx_cash_date (transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reminders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NULL,
  title VARCHAR(200) NOT NULL,
  reminder_date DATE NOT NULL,
  reminder_kind ENUM('dogum_gunu','yildonumu','odeme','diger') NOT NULL DEFAULT 'diger',
  is_done TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rem_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  INDEX idx_rem_date (reminder_date),
  INDEX idx_rem_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE contacts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  contact_name VARCHAR(120) NOT NULL,
  role_title VARCHAR(80) NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(190) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cont_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  INDEX idx_cont_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_notes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  note TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_note_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_note_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_note_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE message_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel ENUM('sms','email') NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  subject VARCHAR(255) NULL,
  body TEXT NULL,
  status ENUM('kuyruk','gonderildi','hata') NOT NULL DEFAULT 'kuyruk',
  user_id INT UNSIGNED NULL,
  sent_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_msg_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_msg_channel (channel),
  INDEX idx_msg_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed: roller
INSERT INTO roles (name, slug) VALUES
('Sistem Yöneticisi', 'admin'),
('Şube Yöneticisi', 'sube'),
('Personel', 'personel'),
('Temsilci', 'temsilci');

INSERT INTO permissions (slug, label, category, sort_order) VALUES
('dashboard', 'Özet panel', 'genel', 1),
('policies', 'Poliçeler', 'islem', 10),
('collections', 'Tahsilatlar', 'islem', 20),
('customers', 'Sigortalılar', 'islem', 30),
('settings_products', 'Ayarlar — Ürünler', 'ayarlar', 40),
('settings_tali', 'Ayarlar — Tali', 'ayarlar', 50),
('users_manage', 'Kullanıcı yönetimi', 'yonetim', 60),
('roles_configure', 'Rol yetkilerini düzenleme', 'yonetim', 70),
('records_delete', 'Kayıt silme (poliçe, sigortalı, ürün, tali vb.)', 'guvenlik', 80);

INSERT INTO role_permissions (role_id, permission_id) SELECT 1, id FROM permissions;
INSERT INTO role_permissions (role_id, permission_id) SELECT 2, id FROM permissions WHERE slug NOT IN ('users_manage', 'roles_configure');
INSERT INTO role_permissions (role_id, permission_id) SELECT 3, id FROM permissions WHERE slug IN ('dashboard', 'policies', 'collections', 'customers');
INSERT INTO role_permissions (role_id, permission_id) SELECT 4, id FROM permissions WHERE slug IN ('dashboard', 'policies', 'customers');

-- Seed: şube
INSERT INTO branches (parent_id, name, code) VALUES (NULL, 'Merkez Şube', 'MRK');

-- Seed: ürünler (çapraz ürün raporları için temel)
INSERT INTO insurance_products (code, name, category, is_active) VALUES
('KASKO', 'Kasko Sigortası', 'arac', 1),
('TRAFIK', 'Trafik Sigortası', 'arac', 1),
('KISA_TRAFIK', 'Kısa Süreli Trafik', 'arac', 1),
('IMM', 'İMM Sigortası', 'sorumluluk', 1),
('YESIL_KART', 'Yeşil Kart Sigortası', 'arac', 1),
('TAMAMLAYICI_SAGLIK', 'Tamamlayıcı Sağlık Sigortası', 'saglik', 1),
('OZEL_SAGLIK', 'Özel Sağlık Sigortası', 'saglik', 1),
('PEMBE_KURDELE', 'Pembe Kurdele Sigortası', 'saglik', 1),
('KONUT', 'Konut Sigortası', 'mulk', 1),
('DASK', 'DASK', 'mulk', 1),
('FERDI_KAZA', 'Ferdi Kaza Sigortası', 'saglik', 1),
('TELEFON_KASKO', 'Telefon Kaskosu', 'diger', 1),
('EVCIL_HAYVAN', 'Evcil Hayvan Sigortası', 'diger', 1),
('SEYAHAT_SAGLIK', 'Seyahat Sağlık Sigortası', 'saglik', 1),
('NAKLIYAT', 'Nakliyat Sigortası', 'nakliyat', 1),
('YURTDISI', 'Yurtdışı Sigortası', 'diger', 1),
('EMTEA', 'Emtea Sigortası', 'diger', 1),
('FFL', 'FFL Sigortası', 'diger', 1),
('TEKNE', 'Tekne Sigortası', 'diger', 1),
('ISYERI', 'İşyeri Sigortası', 'mulk', 1);

-- Seed: müşteri grubu
INSERT INTO customer_groups (name, description) VALUES ('Genel', 'Varsayılan grup');

-- Örnek sigortalı (formlarda seçim için)
INSERT INTO customers (group_id, tali_id, customer_type, name, identity_no, phone, email, city)
VALUES (1, NULL, 'bireysel', 'Örnek Sigortalı', '11111111111', '05001234567', 'ornek@example.com', 'İstanbul');