-- Crest Chimney Admin Panel Database Setup
-- Run this SQL in phpMyAdmin to create the admin users table and default admin account
-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS `crestchimney_db` DEFAULT CHARACTER
SET
  utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `crestchimney_db`;

-- Create admin_users table
CREATE TABLE
  IF NOT EXISTS `admin_users` (
    `id` int (11) NOT NULL AUTO_INCREMENT,
    `username` varchar(50) NOT NULL,
    `password` varchar(255) NOT NULL,
    `email` varchar(100) NOT NULL,
    `full_name` varchar(100) DEFAULT NULL,
    `status` enum ('active', 'inactive') DEFAULT 'active',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `email` (`email`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Insert default admin user
-- Default credentials:
-- Username: admin
-- Password: admin123
-- Email: admin@zibot.store
INSERT INTO
  `admin_users` (
    `username`,
    `password`,
    `email`,
    `full_name`,
    `status`
  )
VALUES
  (
    'admin',
    '$2y$12$jAnnOijXegjNeu3yILbk1untSF7NiD4nky9PbJW3o3vKT9eQ7KE5K',
    'admin@zibot.store',
    'Administrator',
    'active'
  );

-- Create website_settings table
CREATE TABLE
  IF NOT EXISTS `website_settings` (
    `id` int (11) NOT NULL AUTO_INCREMENT,
    `setting_key` varchar(100) NOT NULL,
    `setting_value` text DEFAULT NULL,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `setting_key` (`setting_key`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Insert default website settings
INSERT INTO
  `website_settings` (`setting_key`, `setting_value`)
VALUES
  (
    'logo',
    '/wp-content/uploads/2022/05/CrestChimney-Logo-W-1024x160.png'
  ),
  (
    'favicon',
    '/wp-content/uploads/2022/05/CrestChimney-Favicon-150x150.png'
  ),
  ('website_url', 'http://localhost'),
  ('website_name', 'Crest Chimney'),
  (
    'website_title',
    'Crest Chimney - Fireplace & Chimney Services'
  ),
  ('phone_number', '');

-- Create page_titles table
CREATE TABLE
  IF NOT EXISTS `page_titles` (
    `id` int (11) NOT NULL AUTO_INCREMENT,
    `url` varchar(255) NOT NULL,
    `title` text NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `url` (`url`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Insert all page titles (105+ pages) - See page-titles.sql for complete list
-- Run the extract-titles.php script to regenerate this list if needed
INSERT INTO
  `page_titles` (`url`, `title`)
VALUES
  (
    '/',
    'The Best Fireplace & Chimney Services Near Me - CSIA Certified'
  ),
  (
    '/about-us/',
    'Read More About The 2023 Best Chimney & Fireplace Co. in USA'
  ),
  ('/about-us/careers/', 'Careers - Crest Chimney'),
  ('/about-us/financing/', 'Financing - Crest Chimney'),
  (
    '/about-us/maintenance-program/',
    'Maintenance Program - Crest Chimney'
  ),
  ('/about-us/our-team/', 'Our Team - Crest Chimney'),
  ('/about-us/reviews/', 'Reviews - Crest Chimney'),
  (
    '/blog/',
    'Latest Chimney Updates You Must Know Before The Winter - Blog'
  ),
  (
    '/faq/',
    'Frequently Asked Questions - 2023 FAQ''s - Crest Chimney'
  ),
  (
    '/contact-us/',
    'Contact Us We''re The Best Chimney & Fireplace Company in USA'
  ),
  (
    '/our-services/',
    'Professional Chimney, Fireplace & Masonry Services - Crest Chimney'
  ),
  (
    '/our-locations/',
    'Crest Chimney Providing Best Chimney & Fireplace Services in USA'
  ),
  ('/our-work/', 'Our Work - Crest Chimney'),
  (
    '/online-scheduling/',
    'Online Scheduling - Crest Chimney'
  ),
  ('/privacy-policy/', 'Privacy Policy - Crest Chimney'),
  (
    '/terms-and-conditions/',
    'Terms & Conditions - Crest Chimney'
  ),
  ('/sitemap/', 'Sitemap - Crest Chimney'),
  (
    '/yith-store-locator/',
    '2023 Best Store Locator in the USA - Crest Chimney'
  );

-- Note: For complete list of all 105+ pages, see page-titles.sql file
-- Note: The password hash above is for 'admin123'
-- To change the password, you can use PHP's password_hash() function:
-- password_hash('your_new_password', PASSWORD_DEFAULT)