CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  token VARCHAR(128) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_settings (
  id INT PRIMARY KEY DEFAULT 1,
  organization_name VARCHAR(190) NOT NULL,
  tagline VARCHAR(255) NULL,
  primary_color VARCHAR(20) DEFAULT '#C62828',
  emergency_phone VARCHAR(40) NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS banners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  subtitle VARCHAR(255) NULL,
  image_url TEXT NULL,
  is_active TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS donors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  blood_group VARCHAR(5) NOT NULL,
  district VARCHAR(100) NOT NULL,
  upazila VARCHAR(100) NULL,
  address VARCHAR(255) NULL,
  photo_url TEXT NULL,
  last_donation_date DATE NULL,
  available TINYINT(1) DEFAULT 1,
  verified TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_group (blood_group),
  INDEX idx_district (district),
  INDEX idx_available (available)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS blood_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_problem VARCHAR(255) NOT NULL,
  blood_group VARCHAR(5) NOT NULL,
  bag_count INT NOT NULL DEFAULT 1,
  hemoglobin VARCHAR(30) NULL,
  donation_date DATE NOT NULL,
  donation_time TIME NULL,
  hospital VARCHAR(255) NOT NULL,
  district VARCHAR(100) NULL,
  contact_phone VARCHAR(40) NOT NULL,
  details TEXT NULL,
  status ENUM('pending','approved','completed','cancelled') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_request_status (status),
  INDEX idx_request_group (blood_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaigns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  description TEXT NULL,
  image_url TEXT NULL,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS feature_cards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  section_key VARCHAR(80) NOT NULL,
  title VARCHAR(160) NOT NULL,
  subtitle VARCHAR(255) NULL,
  icon VARCHAR(80) NULL,
  route_key VARCHAR(80) NULL,
  sort_order INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (id, organization_name, tagline, emergency_phone)
VALUES (1,'সাদাকা ব্লাড সোসাইটি ময়মনসিংহ','রক্তের বন্ধনে জুড়ুক প্রাণ, সাদাকা হোক রক্তদান','01700000000')
ON DUPLICATE KEY UPDATE organization_name=VALUES(organization_name), tagline=VALUES(tagline);

INSERT INTO admins(name,email,password_hash)
VALUES ('Administrator','admin@sadaka.local','$2y$10$Pim.7RrD6.mS5Xr79gVw3uz1KXKJ9CczhWyFpOxopfxM4xoOCrmda')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO banners(title,subtitle,image_url,is_active,sort_order)
SELECT 'জীবনের জন্য রক্ত দিন!','সহযোগিতা ও অনুপ্রেরণা: আমাদের দল','https://images.unsplash.com/photo-1615461066841-6116e61058f4?auto=format&fit=crop&w=1200&q=80',1,1
WHERE NOT EXISTS (SELECT 1 FROM banners LIMIT 1);

INSERT INTO feature_cards(section_key,title,subtitle,icon,route_key,sort_order,is_active)
SELECT 'dashboard','রক্তের লাইভ রিকোয়েস্ট সমূহ','আপনার এলাকায় এই মুহূর্তে জরুরি রক্তের পোস্টগুলো দেখুন','water_drop','live_requests',1,1
WHERE NOT EXISTS (SELECT 1 FROM feature_cards WHERE section_key='dashboard' AND route_key='live_requests');
INSERT INTO feature_cards(section_key,title,subtitle,icon,route_key,sort_order,is_active)
SELECT 'dashboard','রক্ত দাতা খুঁজুন','গ্রুপ ও এলাকা ভিত্তিক','search','donors',2,1
WHERE NOT EXISTS (SELECT 1 FROM feature_cards WHERE section_key='dashboard' AND route_key='donors');
INSERT INTO feature_cards(section_key,title,subtitle,icon,route_key,sort_order,is_active)
SELECT 'dashboard','রক্তের আবেদন','দ্রুত রিকোয়েস্ট পাঠান','add_circle','request',3,1
WHERE NOT EXISTS (SELECT 1 FROM feature_cards WHERE section_key='dashboard' AND route_key='request');
INSERT INTO feature_cards(section_key,title,subtitle,icon,route_key,sort_order,is_active)
SELECT 'dashboard','লিডারবোর্ড','সর্বোচ্চ রক্তদাতাদের তালিকা','emoji_events','leaderboard',4,1
WHERE NOT EXISTS (SELECT 1 FROM feature_cards WHERE section_key='dashboard' AND route_key='leaderboard');
INSERT INTO feature_cards(section_key,title,subtitle,icon,route_key,sort_order,is_active)
SELECT 'social','মানবতার গ্যালারি','রক্তদানের স্মরণীয় মুহূর্তগুলো','photo_library','gallery',1,1
WHERE NOT EXISTS (SELECT 1 FROM feature_cards WHERE section_key='social' AND route_key='gallery');
INSERT INTO feature_cards(section_key,title,subtitle,icon,route_key,sort_order,is_active)
SELECT 'social','ক্যাম্পেইন ও ইভেন্টস','ফ্রি ব্লাড ক্যাম্পেইন ও আগামী আপডেট','event','campaigns',2,1
WHERE NOT EXISTS (SELECT 1 FROM feature_cards WHERE section_key='social' AND route_key='campaigns');
INSERT INTO feature_cards(section_key,title,subtitle,icon,route_key,sort_order,is_active)
SELECT 'social','সোশ্যাল ওয়েলফেয়ার','আমাদের মানবিক উদ্যোগ ও সেবাসমূহ','favorite','welfare',3,1
WHERE NOT EXISTS (SELECT 1 FROM feature_cards WHERE section_key='social' AND route_key='welfare');