CREATE DATABASE IF NOT EXISTS solacademy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE solacademy;

DROP TABLE IF EXISTS contact_messages;
DROP TABLE IF EXISTS gallery;
DROP TABLE IF EXISTS testimonials;
DROP TABLE IF EXISTS news;
DROP TABLE IF EXISTS page_content;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS admins;

CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE news (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(200) NOT NULL UNIQUE,
  excerpt TEXT,
  body TEXT NOT NULL,
  image VARCHAR(255),
  status ENUM('draft','published') DEFAULT 'published',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE gallery (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) DEFAULT '',
  image VARCHAR(255) NOT NULL,
  alt_text VARCHAR(200) DEFAULT '',
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE testimonials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  role VARCHAR(100) DEFAULT '',
  content TEXT NOT NULL,
  avatar VARCHAR(255) DEFAULT '',
  rating TINYINT DEFAULT 5,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE contact_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  subject VARCHAR(200) DEFAULT '',
  message TEXT NOT NULL,
  is_read TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE page_content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(50) NOT NULL UNIQUE,
  title VARCHAR(200) DEFAULT '',
  body TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  site_name VARCHAR(100) DEFAULT 'SOL Christian Academy',
  site_email VARCHAR(100) DEFAULT 'admin@solacademy.org.uk',
  site_phone VARCHAR(50) DEFAULT '0161-637-2944',
  site_address VARCHAR(200) DEFAULT '115 Fairfield Street, Manchester, M12 6EL',
  social_links JSON DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO admins (username, email, password_hash) VALUES
('admin', 'admin@solacademy.org.uk', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

INSERT INTO news (title, slug, excerpt, body, image, status) VALUES
('Open Day 2025 – Join Us', 'open-day-2025', 'We invite you to explore SOL Christian Academy at our upcoming Open Day.', '<p>We are delighted to invite prospective families to our annual Open Day. This is a wonderful opportunity to tour our facilities, meet our dedicated staff, and see firsthand how we nurture each child''s potential within a caring Christian environment.</p><p>Date: Saturday, 15th March 2025<br>Time: 10:00 AM – 2:00 PM<br>Location: 115 Fairfield Street, Manchester, M12 6EL</p><p>Booking is essential. Please contact our admissions office to reserve your place.</p>', 'uploads/news/open-day.jpg', 'published'),

('Outstanding GCSE Results 2024', 'gcse-results-2024', 'Our Year 11 students achieved outstanding GCSE results this summer.', '<p>We are incredibly proud of our Year 11 students who have achieved excellent GCSE results this year. With a 98% pass rate and 75% of students achieving grades 9-4 in English and Mathematics, these results reflect the dedication of our students and the unwavering support of our teaching staff.</p><p>Headteacher Mrs. Cisse-Sibi commented: "These results are a testament to the hard work of our students and the personalised approach of our ACE curriculum, which allows every child to progress at their own pace."</p>', 'uploads/news/gcse.jpg', 'published'),

('New Sports Facilities Announced', 'new-sports-facilities', 'We are excited to announce the development of new sports facilities on campus.', '<p>SOL Christian Academy is pleased to announce a significant investment in new sports facilities, including a multi-purpose sports hall, outdoor football pitch, and a dedicated dance studio. These facilities will enhance our extracurricular programme and provide excellent opportunities for physical education and team sports.</p><p>The new facilities are expected to be completed by September 2025 and will be available to all students from Nursery through Sixth Form.</p>', 'uploads/news/sports.jpg', 'published');

INSERT INTO gallery (title, image, alt_text, sort_order) VALUES
('School Library', 'assets/images/gallery-1.jpg', 'Students in the library', 1),
('Classroom Learning', 'assets/images/gallery-2.jpg', 'Students working in class', 2),
('Outdoor Activities', 'assets/images/gallery-3.jpg', 'Students outdoors', 3),
('School Event', 'assets/images/gallery-4.jpg', 'School event gathering', 4),
('Sports Day', 'assets/images/gallery-5.jpg', 'Sports day activities', 5),
('Graduation Ceremony', 'assets/images/gallery-6.jpg', 'Graduation ceremony', 6);

INSERT INTO testimonials (name, role, content, rating) VALUES
('Mrs. Sarah Thompson', 'Parent of Year 6 Student', 'SOL Christian Academy has been a blessing for our family. The individualised ACE curriculum allowed our son to excel at his own pace, and the Christian values taught at school have helped shape his character beautifully.', 5),
('Mr. James Adebayo', 'Parent of Year 9 Student', 'We moved our daughter to SOL Academy two years ago and it was the best decision we have made. The teachers are incredibly supportive, and the sense of community is unlike any other school we have experienced.', 5),
('Dr. Rebecca Owens', 'Parent of Year 12 Student', 'The Sixth Form at SOL Academy has prepared our daughter exceptionally well for university. The leadership opportunities and the focus on character development have given her confidence and a strong moral compass.', 5);

INSERT INTO settings (site_name, site_email, site_phone, site_address) VALUES
('SOL Christian Academy', 'admin@solacademy.org.uk', '0161-637-2944', '115 Fairfield Street, Manchester, M12 6EL');
