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 districts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_districts_active (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS thanas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  district_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_thana_district_name (district_id, name),
  INDEX idx_thanas_district (district_id, is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS blood_groups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(5) NOT NULL UNIQUE,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_blood_groups_active (is_active, sort_order)
) 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 users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NULL UNIQUE,
  phone VARCHAR(40) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  blood_group VARCHAR(5) NULL,
  district VARCHAR(100) NULL,
  photo_url TEXT NULL,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  verified TINYINT(1) NOT NULL DEFAULT 0,
  token VARCHAR(128) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_login_at DATETIME NULL,
  INDEX idx_users_status(status),
  INDEX idx_users_verified(verified),
  INDEX idx_users_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS blood_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  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_user (user_id),
  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;

-- Existing installations may have been created with latin1. Convert before inserting Bangla demo/default data.
ALTER TABLE admins CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE app_settings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE banners CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE districts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE thanas CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE blood_groups CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE donors CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE blood_requests CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE campaigns CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE feature_cards CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Donor filter master data. Admin Panel can add more later.
INSERT INTO districts(name,sort_order,is_active)
VALUES ('ময়মনসিংহ',1,1)
ON DUPLICATE KEY UPDATE is_active=1, sort_order=VALUES(sort_order);

INSERT INTO thanas(district_id,name,sort_order,is_active)
SELECT id,'ময়মনসিংহ সদর',1,1 FROM districts WHERE name='ময়মনসিংহ'
ON DUPLICATE KEY UPDATE is_active=1, sort_order=VALUES(sort_order);

INSERT INTO blood_groups(name,sort_order,is_active) VALUES
('A+',1,1),('B+',2,1),('O+',3,1),('AB+',4,1),('A-',5,1),('B-',6,1),('O-',7,1),('AB-',8,1)
ON DUPLICATE KEY UPDATE is_active=1, sort_order=VALUES(sort_order);

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$12$yyHZ0alntw9ppaySWtWo.enrQcLtzHBAxPkWTYyK8VKKCnt9UkF4C')
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');

-- LifeDrop demo donors. All demo donors intentionally belong to the same seeded thana.
INSERT INTO donors(name,phone,blood_group,district,upazila,address,photo_url,last_donation_date,available,verified)
SELECT 'নেহা শর্মা','01711000001','O+','ময়মনসিংহ','ময়মনসিংহ সদর','চরপাড়া, ময়মনসিংহ','https://i.pravatar.cc/200?img=47',NULL,1,1
WHERE NOT EXISTS (SELECT 1 FROM donors WHERE phone='01711000001');
INSERT INTO donors(name,phone,blood_group,district,upazila,address,photo_url,last_donation_date,available,verified)
SELECT 'রোহান ভার্মা','01711000002','A+','ময়মনসিংহ','ময়মনসিংহ সদর','টাউন হল, ময়মনসিংহ','https://i.pravatar.cc/200?img=12','2026-03-01',1,1
WHERE NOT EXISTS (SELECT 1 FROM donors WHERE phone='01711000002');
INSERT INTO donors(name,phone,blood_group,district,upazila,address,photo_url,last_donation_date,available,verified)
SELECT 'প্রিয়া প্যাটেল','01711000003','B+','ময়মনসিংহ','ময়মনসিংহ সদর','ময়মনসিংহ সদর','https://i.pravatar.cc/200?img=45','2026-07-01',1,1
WHERE NOT EXISTS (SELECT 1 FROM donors WHERE phone='01711000003');
INSERT INTO donors(name,phone,blood_group,district,upazila,address,photo_url,last_donation_date,available,verified)
SELECT 'আরিফ হাসান','01711000004','AB+','ময়মনসিংহ','ময়মনসিংহ সদর','ময়মনসিংহ সদর','https://i.pravatar.cc/200?img=11',NULL,1,1
WHERE NOT EXISTS (SELECT 1 FROM donors WHERE phone='01711000004');
INSERT INTO donors(name,phone,blood_group,district,upazila,address,photo_url,last_donation_date,available,verified)
SELECT 'সাবিনা ইয়াসমিন','01711000005','A-','ময়মনসিংহ','ময়মনসিংহ সদর','ময়মনসিংহ সদর','https://i.pravatar.cc/200?img=32',NULL,1,1
WHERE NOT EXISTS (SELECT 1 FROM donors WHERE phone='01711000005');
INSERT INTO donors(name,phone,blood_group,district,upazila,address,photo_url,last_donation_date,available,verified)
SELECT 'মেহেদী হাসান','01711000006','B-','ময়মনসিংহ','ময়মনসিংহ সদর','ময়মনসিংহ সদর','https://i.pravatar.cc/200?img=15','2026-02-20',1,1
WHERE NOT EXISTS (SELECT 1 FROM donors WHERE phone='01711000006');
INSERT INTO donors(name,phone,blood_group,district,upazila,address,photo_url,last_donation_date,available,verified)
SELECT 'তানজিলা আক্তার','01711000007','O-','ময়মনসিংহ','ময়মনসিংহ সদর','ময়মনসিংহ সদর','https://i.pravatar.cc/200?img=44',NULL,1,1
WHERE NOT EXISTS (SELECT 1 FROM donors WHERE phone='01711000007');
INSERT INTO donors(name,phone,blood_group,district,upazila,address,photo_url,last_donation_date,available,verified)
SELECT 'সিয়াম আহমেদ','01711000008','AB-','ময়মনসিংহ','ময়মনসিংহ সদর','ময়মনসিংহ সদর','https://i.pravatar.cc/200?img=13',NULL,1,1
WHERE NOT EXISTS (SELECT 1 FROM donors WHERE phone='01711000008');

-- If an older patch already inserted these demo phone numbers elsewhere, move them into the seeded thana.
UPDATE donors
SET district='ময়মনসিংহ', upazila='ময়মনসিংহ সদর'
WHERE phone IN ('01711000001','01711000002','01711000003','01711000004','01711000005','01711000006','01711000007','01711000008');