CREATE DATABASE IF NOT EXISTS `7addak` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `7addak`;

CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  status TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE countries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name_en VARCHAR(120) NOT NULL,
  name_ar VARCHAR(120) NOT NULL,
  code VARCHAR(10),
  status TINYINT DEFAULT 1
);

CREATE TABLE cities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  country_id INT NOT NULL,
  name_en VARCHAR(120) NOT NULL,
  name_ar VARCHAR(120) NOT NULL,
  status TINYINT DEFAULT 1,
  FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
);

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name_en VARCHAR(120) NOT NULL,
  name_ar VARCHAR(120) NOT NULL,
  icon VARCHAR(255),
  status TINYINT DEFAULT 1
);

CREATE TABLE businesses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  country_id INT NOT NULL,
  city_id INT NOT NULL,
  name VARCHAR(180) NOT NULL,
  description TEXT,
  phone VARCHAR(40),
  whatsapp VARCHAR(40),
  email VARCHAR(160),
  address VARCHAR(255),
  latitude DECIMAL(10,7),
  longitude DECIMAL(10,7),
  logo VARCHAR(255),
  cover_photo VARCHAR(255),
  menu_link VARCHAR(255),
  website VARCHAR(255),
  facebook VARCHAR(255),
  instagram VARCHAR(255),
  tiktok VARCHAR(255),
  status TINYINT DEFAULT 1,
  is_featured TINYINT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id),
  FOREIGN KEY (country_id) REFERENCES countries(id),
  FOREIGN KEY (city_id) REFERENCES cities(id)
);

CREATE TABLE business_hours (
  id INT AUTO_INCREMENT PRIMARY KEY,
  business_id INT NOT NULL,
  day_key ENUM('mon','tue','wed','thu','fri','sat','sun') NOT NULL,
  open_time TIME NULL,
  close_time TIME NULL,
  is_closed TINYINT DEFAULT 0,
  FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

CREATE TABLE business_gallery (
  id INT AUTO_INCREMENT PRIMARY KEY,
  business_id INT NOT NULL,
  image VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

CREATE TABLE sliders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title_en VARCHAR(160),
  title_ar VARCHAR(160),
  image VARCHAR(255) NOT NULL,
  link VARCHAR(255),
  status TINYINT DEFAULT 1,
  position INT DEFAULT 0
);

CREATE TABLE offers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  business_id INT NULL,
  title_en VARCHAR(180) NOT NULL,
  title_ar VARCHAR(180) NOT NULL,
  description_en TEXT,
  description_ar TEXT,
  image VARCHAR(255),
  start_date DATE,
  end_date DATE,
  status TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE SET NULL
);

CREATE TABLE jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  business_id INT NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT,
  phone VARCHAR(40),
  status TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE SET NULL
);

CREATE TABLE blood_donors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  blood_type ENUM('A+','A-','B+','B-','AB+','AB-','O+','O-') NOT NULL,
  city_id INT,
  is_available TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL
);

CREATE TABLE hospitals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  city_id INT NOT NULL,
  name VARCHAR(180) NOT NULL,
  phone VARCHAR(40),
  address VARCHAR(255),
  latitude DECIMAL(10,7),
  longitude DECIMAL(10,7),
  status TINYINT DEFAULT 1,
  FOREIGN KEY (city_id) REFERENCES cities(id)
);

CREATE TABLE pharmacies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  city_id INT NOT NULL,
  name VARCHAR(180) NOT NULL,
  phone VARCHAR(40),
  address VARCHAR(255),
  latitude DECIMAL(10,7),
  longitude DECIMAL(10,7),
  status TINYINT DEFAULT 1,
  FOREIGN KEY (city_id) REFERENCES cities(id)
);

CREATE TABLE pages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  page_key VARCHAR(80) UNIQUE NOT NULL,
  title_en VARCHAR(160),
  title_ar VARCHAR(160),
  content_en LONGTEXT,
  content_ar LONGTEXT
);

CREATE TABLE dictionary (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lang_key VARCHAR(120) UNIQUE NOT NULL,
  en TEXT,
  ar TEXT,
  fr TEXT NULL
);

INSERT INTO admins(name,email,password) VALUES('Admin','admin@7addak.com', '$2y$10$demo_change_password_hash');
INSERT INTO countries(name_en,name_ar,code) VALUES('Lebanon','لبنان','LB');
INSERT INTO cities(country_id,name_en,name_ar) VALUES(1,'Minieh','المنية'),(1,'Tripoli','طرابلس');
INSERT INTO categories(name_en,name_ar,icon) VALUES
('Restaurants','مطاعم','restaurant'),('Clothes','محلات ألبسة','clothes'),('Electronics','أدوات كهربائية','electric'),('Hospitals','مستشفيات','hospital'),('Pharmacies','صيدليات','pharmacy');
INSERT INTO businesses(category_id,country_id,city_id,name,description,phone,whatsapp,address,latitude,longitude,logo,cover_photo,menu_link,facebook,instagram,is_featured) VALUES
(1,1,1,'Resto Cafe Demo','مطعم وكافيه تجريبي في المنية','+96176442423','+96176442423','Minieh Main Road',34.4489000,35.8297000,'uploads/logos/resto.png','uploads/covers/resto.jpg','https://example.com/menu','https://facebook.com','https://instagram.com',1),
(2,1,1,'Style House','محل ألبسة تجريبي','+96170000000','+96170000000','Minieh Souk',34.4491000,35.8300000,'uploads/logos/style.png','uploads/covers/style.jpg',NULL,NULL,NULL,0);
INSERT INTO business_hours(business_id,day_key,open_time,close_time,is_closed) VALUES
(1,'mon','09:00','23:00',0),(1,'tue','09:00','23:00',0),(1,'wed','09:00','23:00',0),(1,'thu','09:00','23:00',0),(1,'fri','09:00','00:00',0),(1,'sat','09:00','00:00',0),(1,'sun','12:00','23:00',0);
INSERT INTO sliders(title_en,title_ar,image,position) VALUES('Everything near you','كل شي حدك','uploads/sliders/slide1.jpg',1);
INSERT INTO offers(business_id,title_en,title_ar,description_en,description_ar,start_date,end_date) VALUES(1,'20% Burger Offer','عرض 20% على البرغر','Today only','اليوم فقط',CURDATE(),DATE_ADD(CURDATE(), INTERVAL 10 DAY));
INSERT INTO blood_donors(full_name,phone,blood_type,city_id) VALUES('Ahmad Demo','+96171000000','O+',1),('Sara Demo','+96103000000','A+',1);
INSERT INTO hospitals(city_id,name,phone,address) VALUES(1,'Minieh Government Hospital','+961000000','Minieh');
INSERT INTO pharmacies(city_id,name,phone,address) VALUES(1,'Pharmacy Demo','+961000001','Minieh');
INSERT INTO pages(page_key,title_en,title_ar,content_en,content_ar) VALUES
('about','About 7addak','عن حدك','Directory app for local services.','تطبيق يجمع المؤسسات والخدمات حولك.'),
('privacy','Privacy Policy','سياسة الخصوصية','Your data is protected.','بياناتك محمية.'),
('terms','Terms & Conditions','الشروط والأحكام','Use the app respectfully.','استخدم التطبيق بشكل صحيح.');
INSERT INTO dictionary(lang_key,en,ar,fr) VALUES
('app_name','7addak','حدك','7addak'),('search','Search','بحث','Recherche'),('open','Open','مفتوح','Ouvert'),('closed','Closed','مغلق','Fermé'),('call_now','Call Now','اتصل الآن','Appeler');