-- 1. Admins Table
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `role` VARCHAR(20) DEFAULT 'admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default Admin Account
INSERT INTO `admins` (`username`, `password`, `email`) VALUES
('admin', '$2y$10$e.w6uV.5gTz3l2p2a2x2uOq5zZ.z3Z3z3Z3z3Z3z3Z3z3Z3z3Z3z3', 'admin@tnexplorer.com')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- 2. Categories Table
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  `slug` VARCHAR(50) NOT NULL UNIQUE,
  `icon_name` VARCHAR(50) NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `categories` (`id`, `name`, `slug`, `icon_name`, `description`) VALUES
(1, 'Heritage & Temples', 'heritage', 'account_balance', 'Ancient Dravidian architecture, sacred temples, and UNESCO world heritage monuments.'),
(2, 'Hill Stations', 'hill-stations', 'landscape', 'Serene mountain ranges, tea plantations, and cool weather in the Western Ghats.'),
(3, 'Beaches & Coastal', 'beaches', 'beach_access', 'Scenic coastlines along the Bay of Bengal and Gulf of Mannar.'),
(4, 'Waterfalls & Nature', 'waterfalls', 'waterfall_chart', 'Cascade waterfalls, lush forest streams, and natural eco-spots.'),
(5, 'Wildlife & Sanctuaries', 'wildlife', 'pets', 'Tiger reserves, bird sanctuaries, and rich bio-reserves.')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- 3. Places Table
CREATE TABLE IF NOT EXISTS `places` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `district` VARCHAR(100) NOT NULL,
  `short_description` VARCHAR(255) NOT NULL,
  `full_description` TEXT NOT NULL,
  `image_url` VARCHAR(500) NOT NULL,
  `latitude` DECIMAL(10, 8) NOT NULL,
  `longitude` DECIMAL(11, 8) NOT NULL,
  `best_time_to_visit` VARCHAR(100) NOT NULL,
  `entry_fee` VARCHAR(100) NOT NULL,
  `opening_hours` VARCHAR(100) NOT NULL,
  `cultural_tips` TEXT NOT NULL,
  `local_tips` TEXT NOT NULL,
  `is_featured` TINYINT(1) DEFAULT 0,
  `rating` DECIMAL(3, 2) DEFAULT 4.80,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `places` (`id`, `category_id`, `name`, `district`, `short_description`, `full_description`, `image_url`, `latitude`, `longitude`, `best_time_to_visit`, `entry_fee`, `opening_hours`, `cultural_tips`, `local_tips`, `is_featured`, `rating`) VALUES
(1, 1, 'Brihadisvara Temple (Big Temple)', 'Thanjavur', '1000-year-old Chola architectural masterpiece and UNESCO World Heritage site.', 'Built by Emperor Rajaraja Chola I between 1003 and 1010 CE, Brihadisvara Temple is one of the grandest Dravidian temples in India. The octagonal Vimana tower stands 66 meters tall, crowned by a massive 81-tonne single granite block capstone.', 'https://images.unsplash.com/photo-1600100397608-f010f423b971?auto=format&fit=crop&w=800&q=80', 10.78280000, 79.13180000, 'November to February', 'Free (Monuments: ₹50 / $5 for Foreigners)', '06:00 AM - 12:30 PM & 04:00 PM - 08:30 PM', 'Remove footwear before entering main sanctum. Wear conservative attire covering shoulders and knees. Photography is prohibited inside the inner sanctum.', 'Visit during early morning or evening sunset for spectacular golden lighting on the granite stone carving.', 1, 4.95),
(2, 1, 'Shore Temple & Rock Relieffs', 'Chengalpattu (Mahabalipuram)', '7th-century Pallava coastal temples overlooking the Bay of Bengal.', 'Mahabalipuram features magnificent monolith rock-cut monuments created by the Pallava Dynasty. Highlights include Arjuna’s Penance, Pancha Rathas, and the majestic Shore Temple bathed in coastal ocean waves.', 'https://images.unsplash.com/photo-1582510003544-4d00b7f74220?auto=format&fit=crop&w=800&q=80', 12.61690000, 80.19920000, 'October to March', '₹40 for Indians, ₹600 ($8) for Foreigners', '06:00 AM - 06:00 PM', 'Do not climb or scratch ancient rock sculptures. Keep hydrated as coastal weather is humid.', 'Rent a bicycle to explore all monuments conveniently in one day. Try local fresh seafood nearby.', 1, 4.88),
(3, 1, 'Meenakshi Amman Temple', 'Madurai', 'Iconic temple complex featuring 14 towering gopurams decorated with thousands of statues.', 'Located in the ancient city of Madurai, Meenakshi Sundareswarar Temple is a masterpiece of Nayak architecture. The complex houses 14 gopurams (gateway towers) up to 50m in height and the famous Hall of 1000 Pillars.', 'https://images.unsplash.com/photo-1627894483216-2138af692e32?auto=format&fit=crop&w=800&q=80', 9.91950000, 78.11930000, 'October to March', 'Free entry (Hall of 1000 Pillars: ₹50)', '05:00 AM - 12:30 PM & 04:00 PM - 10:00 PM', 'Strict dress code: Long pants/dhotis for men, sarees/salwars for women. Electronic items/cameras are not allowed inside.', 'Do not miss the evening Night Ceremony (Sallapam) at 09:00 PM when Lord Shiva’s idol is carried to Goddess Meenakshi’s chamber.', 1, 4.92),
(4, 2, 'Ooty (Udhagamandalam) Hill Station', 'Nilgiris', 'Queen of Hill Stations surrounded by mist-covered Nilgiri mountains and tea gardens.', 'Ooty offers rolling green hills, dense pine forests, colonial architecture, and the heritage UNESCO Nilgiri Mountain Toy Train built by the British in 1908.', 'https://images.unsplash.com/photo-1596895111956-bf1cf0599ce5?auto=format&fit=crop&w=800&q=80', 11.41020000, 76.69500000, 'October to June', 'Free (Toy Train: ₹200 - ₹600)', 'Open all day (Botanical Garden 07:00 AM - 06:30 PM)', 'Respect mountain ecology. Avoid plastic littering in hill sanctuaries.', 'Take the toy train from Coonoor to Ooty for unmatched mountain landscape photos. Try Ooty handmade chocolates.', 1, 4.85),
(5, 2, 'Kodaikanal Lake & Pillar Rocks', 'Dindigul', 'The Princess of Hill Stations featuring star-shaped lakes and misty granite pillars.', 'Kodaikanal sits at 2,133 meters altitude on the Palani Hills. It features lush forests, Bryant Park, Coaker’s Walk with valley views, and serene boat rides on Kodai Lake.', 'https://images.unsplash.com/photo-1589308078059-be1415eab4c3?auto=format&fit=crop&w=800&q=80', 10.23810000, 77.48920000, 'September to May', 'Free (Boating: ₹300 - ₹600)', '24 Hours (Park: 09:00 AM - 06:00 PM)', 'Temperatures drop sharply in evenings; carry warm clothing.', 'Walk along Coaker’s Walk early morning for a spectacular cloud bed view. Rent a tandem bicycle around Kodai Lake.', 1, 4.80),
(6, 3, 'Kanyakumari Sunset & Vivekananda Memorial', 'Kanyakumari', 'Southernmost tip of mainland India where the Bay of Bengal, Arabian Sea, and Indian Ocean meet.', 'Kanyakumari is famous for unique sunrise and sunset over the ocean confluence, the grand Thiruvalluvar Statue (133 ft stone monument), and the rock memorial dedicated to Swami Vivekananda.', 'https://images.unsplash.com/photo-1609949279531-cf48d64bed89?auto=format&fit=crop&w=800&q=80', 8.08830000, 77.53850000, 'October to March', 'Ferry: ₹75 (Express ₹200)', '08:00 AM - 04:00 PM (Ferry Service)', 'Wear life jackets on ferry rides. Modest dress recommended near temples.', 'On full moon days, you can watch sunset and moonrise simultaneously over the ocean horizon.', 1, 4.87)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- 4. Distance Matrix Table
CREATE TABLE IF NOT EXISTS `distance_matrix` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `origin_name` VARCHAR(100) NOT NULL,
  `destination_name` VARCHAR(100) NOT NULL,
  `distance_km` INT NOT NULL,
  `car_time_mins` INT NOT NULL,
  `bus_time_mins` INT NOT NULL,
  `train_time_mins` INT NOT NULL,
  `flight_time_mins` INT DEFAULT 0,
  `estimated_car_cost_inr` INT NOT NULL,
  `notes` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `route_pair` (`origin_name`, `destination_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `distance_matrix` (`origin_name`, `destination_name`, `distance_km`, `car_time_mins`, `bus_time_mins`, `train_time_mins`, `flight_time_mins`, `estimated_car_cost_inr`, `notes`) VALUES
('Chennai Airport (MAA)', 'Mahabalipuram', 55, 75, 105, 0, 0, 1400, 'Scenic East Coast Road (ECR) highway trip'),
('Chennai Airport (MAA)', 'Thanjavur (Tanjore)', 340, 360, 420, 390, 0, 5200, 'Via NH38 through Trichy or Viluppuram'),
('Chennai Airport (MAA)', 'Madurai', 460, 480, 540, 450, 70, 7000, 'Direct flights available from MAA to IXM'),
('Chennai Airport (MAA)', 'Ooty', 540, 600, 660, 570, 60, 8500, 'Flight to Coimbatore (CJB) then 2.5 hrs car drive'),
('Chennai Airport (MAA)', 'Kanyakumari', 700, 720, 780, 690, 80, 11000, 'Flight to Tuticorin/Trivandrum then taxi'),
('Mahabalipuram', 'Pondicherry', 95, 110, 135, 0, 0, 2200, 'Coastal ECR drive along sandy beaches'),
('Pondicherry', 'Thanjavur (Tanjore)', 175, 210, 240, 220, 0, 3200, 'Via Chidambaram Heritage Corridor'),
('Thanjavur (Tanjore)', 'Madurai', 190, 190, 220, 180, 0, 3100, 'Via Trichy NH83 smooth expressway'),
('Madurai', 'Rameshwaram', 170, 180, 210, 190, 0, 2800, 'Cross the iconic Pamban Bridge over the ocean'),
('Madurai', 'Kodaikanal', 115, 160, 190, 0, 0, 2400, 'Ghat road with scenic mountain turns'),
('Madurai', 'Kanyakumari', 245, 230, 270, 210, 0, 3800, 'NH44 North-South corridor route'),
('Coimbatore Airport (CJB)', 'Ooty', 85, 150, 180, 210, 0, 2200, 'Famous ghat road with 36 hairpin bends')
ON DUPLICATE KEY UPDATE `distance_km`=VALUES(`distance_km`);