-- LexSite schema
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','editor') NOT NULL DEFAULT 'admin',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  template VARCHAR(100) NOT NULL DEFAULT 'page.php',
  excerpt VARCHAR(255) DEFAULT NULL,
  content MEDIUMTEXT,
  seo_title VARCHAR(255) DEFAULT NULL,
  seo_desc VARCHAR(255) DEFAULT NULL,
  hero_image VARCHAR(255) DEFAULT NULL,
  published TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS menus (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS menu_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  menu_id INT NOT NULL,
  label VARCHAR(150) NOT NULL,
  url VARCHAR(255) DEFAULT NULL,
  page_id INT DEFAULT NULL,
  parent_id INT DEFAULT NULL,
  sort INT NOT NULL DEFAULT 0,
  FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE,
  FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS media (
  id INT AUTO_INCREMENT PRIMARY KEY,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  mime VARCHAR(100) NOT NULL,
  size INT NOT NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS form_submissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  form VARCHAR(100) NOT NULL,
  data_json JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed admin user (password: admin@123) — change immediately!
INSERT INTO users(name,email,password_hash,role) VALUES
('Site Admin','admin@example.com','$2y$10$6Q8uIuvqVn83C0Z7uI0sreA1dbJX1i8Y4cQ4X5yQO5tZzHc7g4b3i','admin')
ON DUPLICATE KEY UPDATE email=email;

-- Seed minimal content
INSERT IGNORE INTO pages(title,slug,template,excerpt,content,seo_title,seo_desc,hero_image,published,sort_order) VALUES
('Home','home','home.php','Strategic, outcome-focused legal counsel.','<p>Welcome to our chambers. We deliver focused, strategic representation.</p>','Abhimanyu Chopra - Advocate','Leading legal counsel','',1,0),
('About','about','page.php','Profile & credentials','<p>Abhimanyu Chopra is an advocate with expertise in...</p>',NULL,NULL,'',1,10),
('Practice Areas','practice-areas','page.php','What we do','<p>Explore our areas of expertise.</p>',NULL,NULL,'',1,20),
('Contact','contact','contact.php','Book a consultation','<p>We respond within 1 business day.</p>',NULL,NULL,'',1,30);
INSERT IGNORE INTO menus(id,name) VALUES (1,'Primary');
INSERT IGNORE INTO menu_items(menu_id,label,page_id,sort) VALUES (1,'Home',1,0),(1,'About',2,10),(1,'Practice Areas',3,20),(1,'Contact',4,30);

INSERT INTO settings(`key`,`value`) VALUES
('site_title','Abhimanyu Chopra & Associates'),
('site_tagline','Advocates & Legal Consultants'),
('primary_menu_id','1')
ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);