-- ========================================================================
-- জাগীর ক্যাবল নেটওয়ার্ক (ZCN) - সম্পূর্ণ MySQL ডাটাবেজ স্কিমা
-- এই SQL ফাইলটি আপনার cPanel phpMyAdmin-এ গিয়ে Import করুন
-- ========================================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ১. গ্রাহক সংযোগ আবেদন টেবিল (connection_leads)
CREATE TABLE IF NOT EXISTS `connection_leads` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `lead_code` VARCHAR(60) NOT NULL UNIQUE,
  `customer_name` VARCHAR(150) NOT NULL,
  `mobile_number` VARCHAR(30) NOT NULL,
  `address` TEXT NOT NULL,
  `selected_package` VARCHAR(100) NOT NULL,
  `preferred_date` VARCHAR(50) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `admin_notes` TEXT DEFAULT NULL,
  `status` ENUM('new', 'contacted', 'connected') NOT NULL DEFAULT 'new',
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_lead_status` (`status`),
  INDEX `idx_mobile` (`mobile_number`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ২. অফিস ও যোগাযোগ তথ্য টেবিল (business_settings)
CREATE TABLE IF NOT EXISTS `business_settings` (
  `id` INT PRIMARY KEY DEFAULT 1,
  `name_bangla` VARCHAR(255) NOT NULL DEFAULT 'জাগীর ক্যাবল নেটওয়ার্ক',
  `name_english` VARCHAR(255) NOT NULL DEFAULT 'Zagir Cable Network (ZCN)',
  `primary_phone` VARCHAR(50) NOT NULL DEFAULT '01712-893584',
  `secondary_phone` VARCHAR(50) NOT NULL DEFAULT '01972-893584',
  `email` VARCHAR(100) NOT NULL DEFAULT 'zagircablenetwork@gmail.com',
  `location` VARCHAR(255) NOT NULL DEFAULT 'জাগীর বাসস্ট্যান্ড, ঢাকা-আরিচা মহাসড়ক, মানিকগঞ্জ সদর',
  `top_notice` VARCHAR(255) NOT NULL DEFAULT '🎉 শুভ উদ্বোধন! যেকোনো নতুন সংযোগে পাচ্ছেন ফ্রি ফাইবার অপটিক্যাল ক্যাবল ও ইন্সটলেশন!',
  `coverage_radius` VARCHAR(50) NOT NULL DEFAULT '১৫+ কিমি',
  `support_hours` VARCHAR(100) NOT NULL DEFAULT 'সকাল ৯:০০ - রাত ১০:০০',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `business_settings` (`id`, `name_bangla`, `name_english`, `primary_phone`, `secondary_phone`, `email`, `location`, `top_notice`, `coverage_radius`)
VALUES (1, 'জাগীর ক্যাবল নেটওয়ার্ক', 'Zagir Cable Network (ZCN)', '01712-893584', '01972-893584', 'zagircablenetwork@gmail.com', 'জাগীর বাসস্ট্যান্ড, ঢাকা-আরিচা মহাসড়ক, মানিকগঞ্জ সদর', '🎉 শুভ উদ্বোধন! যেকোনো নতুন সংযোগে পাচ্ছেন ফ্রি ফাইবার অপটিক্যাল ক্যাবল ও ইন্সটলেশন!', '১৫+ কিমি');

-- ৩. ইন্টারনেট প্যাকেজ টেবিল (internet_packages)
CREATE TABLE IF NOT EXISTS `internet_packages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `pkg_id` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `speed` INT NOT NULL,
  `speed_unit` VARCHAR(20) NOT NULL DEFAULT 'Mbps',
  `price` DECIMAL(10,2) NOT NULL,
  `validity` VARCHAR(50) NOT NULL DEFAULT '৩০ দিন',
  `features` TEXT NOT NULL,
  `is_popular` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_pkg_active` (`is_active`),
  INDEX `idx_pkg_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `internet_packages` (`pkg_id`, `name`, `speed`, `speed_unit`, `price`, `validity`, `features`, `is_popular`, `is_active`, `display_order`) VALUES
('pkg_reg_15', 'রেগুলার প্লাস', 15, 'Mbps', 500.00, '৩০ দিন', '["বাফারলেস ইউটিউব ও ফেসবুক ব্রাউজিং","২৪/৭ নিরবচ্ছিন্ন অপটিক্যাল ফাইবার সংযোগ","অনলাইন ক্লাস ও পরীক্ষার জন্য সেরা","২৪ ঘণ্টা কাস্টমার সাপোর্ট"]', 0, 1, 1),
('pkg_std_25', 'স্ট্যান্ডার্ড সুপার', 25, 'Mbps', 700.00, '৩০ দিন', '["HD / 4K স্ট্রিমিং ও দ্রুত ডাউনলোড","মাল্টি-ডিভাইস ফ্যামিলি শেয়ারিং উপযোগী","BDIX ও FTP ক্যাশ স্পিড সর্বোচ্চ ৫০ Mbps","জরুরি ব্যাকআপ অপটিক্যাল লাইন"]', 1, 1, 2),
('pkg_spr_40', 'প্রো আল্ট্রা', 40, 'Mbps', 1000.00, '৩০ দিন', '["হেভি ডাউনলোড ও ৪কে ভিডিও স্ট্রিমিং","স্মুথ অনলাইন গেমিং ও লো-পিং রেসপন্স","আনলিমিটেড ডিভাইস ব্যবহার ও হাই-স্ট্যাবিলিটি","ভিআইপি টেকনিক্যাল সাপোর্ট"]', 0, 1, 3),
('pkg_ult_60', 'গিগাফাস্ট সুপার', 60, 'Mbps', 1200.00, '৩০ দিন', '["ফ্রিল্যান্সার, ইউটিউবার ও হোম অফিস সেরা","আল্ট্রা হাই-স্পিড সিমেট্রিক আপলোড/ডাউনলোড","রিয়েল-টাইম ক্লাউড ব্যাকআপ সুবিধা","অগ্রাধিকার ভিত্তিতে টেকনিক্যাল সেবা"]', 0, 1, 4),
('pkg_ent_100', 'এন্টারপ্রাইজ পাওয়ার', 100, 'Mbps', 1800.00, '৩০ দিন', '["অফিস, কর্পোরেট ও হেভি প্রফেশনাল ব্যবহারের জন্য","রিয়েল-আইপি / ডেডিকেটেড ব্যান্ডউইথ সুবিধা","জিরো প্যাকেট ড্রপ গ্যারান্টি","ডেডিকেটেড নেটওয়ার্ক ইঞ্জিনিয়ার সাপোর্ট"]', 0, 1, 5);

-- ৪. এডমিন ইউজার ও পাসওয়ার্ড টেবিল (admin_users)
-- পুরানো বেমানান টেবিল থাকলে ড্রপ করে নতুন তৈরি করবে
DROP TABLE IF EXISTS `admin_users`;

CREATE TABLE `admin_users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(60) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) DEFAULT 'ZCN Super Admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ডিফল্ট এডমিন পাসওয়ার্ড: zcn@2026 (নিরাপদ ও শক্তিশালী bcrypt হ্যাশে সংরক্ষিত)
INSERT INTO `admin_users` (`username`, `password_hash`, `full_name`)
VALUES ('admin', '$2y$12$duy7G4Bh5oZhIDkoau1sHOji7J.mmfBFWzhA9dSjNoTk2kvTDir8.', 'ZCN Super Admin');

-- ৫. কভারেজ এলাকা টেবিল (coverage_areas)
CREATE TABLE IF NOT EXISTS `coverage_areas` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `area_code` VARCHAR(60) NOT NULL UNIQUE,
  `name` VARCHAR(150) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `is_core` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_area_active` (`is_active`),
  INDEX `idx_area_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `coverage_areas` (`area_code`, `name`, `description`, `is_core`, `is_active`, `display_order`) VALUES
('cov_1', 'পুলিশ ক্যাম্প বাজার', 'কোর অফিস এলাকা (১০০% কভারেজ)', 1, 1, 1),
('cov_2', 'মেধশিমুল ও আশপাশ', 'সম্পূর্ণ সক্রিয় অপটিক্যাল ফাইবার', 0, 1, 2),
('cov_3', 'বিসিক শিল্প নগরী', 'আবাসিক ও বাণিজ্যিক সংযোগ প্রস্তুত', 0, 1, 3),
('cov_4', 'মনা বাজার', 'ব্রডব্যান্ড সংযোগ চলমান', 0, 1, 4),
('cov_5', 'জাগীর ইউনিয়ন পরিষদ', 'সরাসরি গিগাবিট ব্যাকবোন লাইন', 0, 1, 5),
('cov_6', 'বউ বাজার', 'দ্রুত ইনস্টলেশন সুবিধা', 0, 1, 6),
('cov_7', 'জাগীর বন্দর আড়ৎ সংলগ্ন', 'জাগীর সীমানা অন্তর্ভুক্ত', 0, 1, 7),
('cov_8', 'গোলড়া চরখন্ড', 'ফাইবার নেটওয়ার্ক সম্প্রসারিত', 0, 1, 8);

-- ========================================================================
-- ৬. গ্রাহক আবেদন ট্র্যাকিং সিস্টেম (applications)
-- Format: ZCN-YYYYMMDD-XXXXX
-- ========================================================================
CREATE TABLE IF NOT EXISTS `applications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `app_id` VARCHAR(60) NOT NULL UNIQUE,
  `customer_name` VARCHAR(150) NOT NULL,
  `mobile_number` VARCHAR(30) NOT NULL,
  `address` TEXT NOT NULL,
  `selected_package` VARCHAR(100) NOT NULL,
  `preferred_date` VARCHAR(50) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `current_status` ENUM('Submitted', 'Under Review', 'Approved', 'Scheduled', 'Installation Complete', 'More Information Required', 'Rejected') NOT NULL DEFAULT 'Submitted',
  `admin_notes` TEXT DEFAULT NULL,
  `scheduled_date` VARCHAR(50) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_app_id` (`app_id`),
  INDEX `idx_app_mobile` (`mobile_number`),
  INDEX `idx_app_status` (`current_status`),
  INDEX `idx_app_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ৭. আবেদন স্ট্যাটাস পরিবর্তন ইতিহাস (application_status_history)
CREATE TABLE IF NOT EXISTS `application_status_history` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `application_id` INT NOT NULL,
  `app_id` VARCHAR(60) NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `status_label_bn` VARCHAR(100) NOT NULL,
  `admin_notes` TEXT DEFAULT NULL,
  `created_by` VARCHAR(100) DEFAULT 'Admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_hist_appid` (`app_id`),
  INDEX `idx_hist_appfk` (`application_id`),
  INDEX `idx_hist_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- প্রাথমিক ডেমো আবেদন ও হিস্ট্রি
INSERT IGNORE INTO `applications` (`id`, `app_id`, `customer_name`, `mobile_number`, `address`, `selected_package`, `preferred_date`, `message`, `current_status`, `admin_notes`, `created_at`) VALUES
(1, 'ZCN-20260924-00125', 'মোঃ রফিকুল ইসলাম', '01812345678', 'পুলিশ ক্যাম্প বাজার সংলগ্ন, জাগীর, মানিকগঞ্জ', '20 Mbps - ৳১২০০ (জনপ্রিয়)', '2026-09-25', 'জরুরি ভিত্তিতে সংযোগ প্রয়োজন। রাউটার আছে কি না জানাবেন।', 'Under Review', 'গ্রাহকের সাথে কথা হয়েছে। শুক্রবার টেকনিশিয়ান ভিজিট করবে।', '2026-09-24 10:20:00'),
(2, 'ZCN-20260924-00126', 'ফারহানা আক্তার', '01798765432', 'জাগীর হাই স্কুল রোড, বাড়ি নং ৪২', '10 Mbps - ৳৮০০', '2026-09-26', 'অনলাইন ক্লাস ও পরীক্ষার জন্য সংযোগ নিব।', 'Approved', 'কাগজপত্র ও কভারেজ যাচাই সম্পন্ন। টেকনিক্যাল টিমের শিডিউল পেন্ডিং।', '2026-09-24 11:05:00'),
(3, 'ZCN-20260924-00127', 'সোহেল রানা (দোকান মালিক)', '01911223344', 'জাগীর বাজার মেইন রোড, মানিকগঞ্জ', '30 Mbps - ৳১৫০০', '2026-09-20', 'দোকানের ক্যাশ কাউন্টারের জন্য দ্রুত সংযোগ লাগবে।', 'Installation Complete', 'সংযোগ সফলভাবে স্থাপন সম্পন্ন হয়েছে। ফাইবার সিগন্যাল -18dBm।', '2026-09-24 09:15:00');

INSERT IGNORE INTO `application_status_history` (`application_id`, `app_id`, `status`, `status_label_bn`, `admin_notes`, `created_by`, `created_at`) VALUES
(1, 'ZCN-20260924-00125', 'Submitted', 'আবেদন জমা হয়েছে', 'অনলাইন আবেদন ফরম থেকে সফলভাবে জমা হয়েছে।', 'Customer', '2026-09-24 10:20:00'),
(1, 'ZCN-20260924-00125', 'Under Review', 'তথ্য যাচাই চলছে', 'গ্রাহকের সাথে কথা হয়েছে। শুক্রবার টেকনিশিয়ান ভিজিট করবে।', 'Admin', '2026-09-24 11:30:00'),
(2, 'ZCN-20260924-00126', 'Submitted', 'আবেদন জমা হয়েছে', 'অনলাইন আবেদন ফরম থেকে সফলভাবে জমা হয়েছে।', 'Customer', '2026-09-24 11:05:00'),
(2, 'ZCN-20260924-00126', 'Under Review', 'তথ্য যাচাই চলছে', 'ঠিকানা ও ফাইবার অপটিক্যাল ক্যাবল রুট যাচাই করা হয়েছে।', 'Admin', '2026-09-24 11:45:00'),
(2, 'ZCN-20260924-00126', 'Approved', 'আবেদন অনুমোদিত', 'কাগজপত্র ও কভারেজ যাচাই সম্পন্ন। টেকনিক্যাল টিমের শিডিউল পেন্ডিং।', 'Admin', '2026-09-24 12:10:00'),
(3, 'ZCN-20260924-00127', 'Submitted', 'আবেদন জমা হয়েছে', 'অনলাইন আবেদন ফরম থেকে সফলভাবে জমা হয়েছে।', 'Customer', '2026-09-24 09:15:00'),
(3, 'ZCN-20260924-00127', 'Under Review', 'তথ্য যাচাই চলছে', 'বাণিজ্যিক সংযোগের সম্ভাব্যতা যাচাই সম্পন্ন।', 'Admin', '2026-09-24 09:40:00'),
(3, 'ZCN-20260924-00127', 'Approved', 'আবেদন অনুমোদিত', 'অনুমোদিত।', 'Admin', '2026-09-24 10:00:00'),
(3, 'ZCN-20260924-00127', 'Scheduled', 'সংযোগ দেওয়ার জন্য নির্ধারিত', 'টেকনিশিয়ান টিম পাঠানো হয়েছে।', 'Admin', '2026-09-24 10:30:00'),
(3, 'ZCN-20260924-00127', 'Installation Complete', 'সংযোগ স্থাপন সম্পন্ন', 'সংযোগ সফলভাবে স্থাপন সম্পন্ন হয়েছে। ফাইবার সিগন্যাল -18dBm।', 'Admin', '2026-09-24 11:50:00');