-- LifeDrop location filters + demo donors.
-- Safe to import more than once into the existing mediheal_blood database.
SET NAMES utf8mb4;

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;

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);

ALTER TABLE donors CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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');

UPDATE donors
SET district='ময়মনসিংহ', upazila='ময়মনসিংহ সদর'
WHERE phone IN ('01711000001','01711000002','01711000003','01711000004','01711000005','01711000006','01711000007','01711000008');