-- nav_panel_skeleton_v1.sql
-- Minimal schema for dynamic bottom navbar + roles/permissions example.
-- Compatible with MariaDB/MySQL.


CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(190) NOT NULL,
  role_code VARCHAR(50) NOT NULL DEFAULT 'customer',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  perm_code VARCHAR(100) NOT NULL,
  UNIQUE KEY uq_user_perm (user_id, perm_code),
  CONSTRAINT fk_user_permissions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nav_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(80) NOT NULL,
  title VARCHAR(120) NOT NULL,
  icon VARCHAR(80) NULL,
  `type` ENUM('link','dropdown') NOT NULL DEFAULT 'link',
  url VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 100,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  parent_id INT NULL,
  role_code VARCHAR(50) NULL,
  permission_code VARCHAR(100) NULL,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_nav_key_parent (`key`, parent_id),
  KEY idx_parent (parent_id),
  KEY idx_active (is_active),
  CONSTRAINT fk_nav_parent FOREIGN KEY (parent_id) REFERENCES nav_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed demo users (password: demo1234)
INSERT IGNORE INTO users (id,email,password_hash,display_name,role_code) VALUES
(1,'admin@example.com','$2y$10$J/4xkE8vKzHn1j0QvH2y7e3Jx6hUq5Gx9S9wZx0j9GqGq6mF7u7mK','Admin Kullanıcı','admin'),
(2,'staff@example.com','$2y$10$J/4xkE8vKzHn1j0QvH2y7e3Jx6hUq5Gx9S9wZx0j9GqGq6mF7u7mK','Personel Kullanıcı','staff'),
(3,'customer@example.com','$2y$10$J/4xkE8vKzHn1j0QvH2y7e3Jx6hUq5Gx9S9wZx0j9GqGq6mF7u7mK','Müşteri Kullanıcı','customer');

-- Give admin settings permission
INSERT IGNORE INTO user_permissions (user_id, perm_code) VALUES
(1,'settings.manage');

-- Seed navbar (root)
INSERT IGNORE INTO nav_items (id,`key`,title,icon,`type`,url,sort_order,is_active,parent_id,role_code,permission_code) VALUES
(10,'home','Ana Sayfa','bi-house','link','/home.php',10,1,NULL,NULL,NULL),
(20,'new_ticket','Talep','bi-plus-circle','link','/talep_olustur.php',20,1,NULL,NULL,NULL),
(30,'track','Takip','bi-pin-map','link','/talep_takip.php',30,1,NULL,NULL,NULL),
(40,'done','Tamamlanan','bi-check2-circle','link','/tamamlanan.php',40,1,NULL,NULL,NULL),
(50,'profile','Profil','bi-person','dropdown',NULL,50,1,NULL,NULL,NULL);

-- Seed profile dropdown children
INSERT IGNORE INTO nav_items (`key`,title,icon,`type`,url,sort_order,is_active,parent_id,role_code,permission_code) VALUES
('profile_me','Kullanıcı Bilgilerim','bi-person-badge','link','/profil.php',10,1,50,NULL,NULL),
('profile_notifications','Bildirimler','bi-bell','link','/bildirimler.php',20,1,50,NULL,NULL),
('profile_settings','Site Ayarları','bi-gear','link','/ayarlar_navbar.php',30,1,50,NULL,'settings.manage'),
('profile_logout','Çıkış','bi-box-arrow-right','link','/logout.php',90,1,50,NULL,NULL);
