-- ═══════════════════════════════════════════════════════════════════════
--  NTPPA CMS — database schema + starter content
--  Import this in cPanel → phpMyAdmin (see DEPLOY-CPANEL.md).
--  Safe to re-import: it drops and recreates its own tables.
-- ═══════════════════════════════════════════════════════════════════════
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sections;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS media;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS pages;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) 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 pages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(160) NOT NULL UNIQUE,
  title VARCHAR(200) NOT NULL,
  nav_label VARCHAR(120) DEFAULT NULL,
  hero_eyebrow VARCHAR(200) DEFAULT NULL,
  hero_heading VARCHAR(255) DEFAULT NULL,
  hero_subtext TEXT DEFAULT NULL,
  hero_image VARCHAR(255) DEFAULT NULL,
  hero_show TINYINT(1) NOT NULL DEFAULT 1,
  meta_title VARCHAR(255) DEFAULT NULL,
  meta_description VARCHAR(320) DEFAULT NULL,
  meta_keywords VARCHAR(320) DEFAULT NULL,
  canonical VARCHAR(255) DEFAULT NULL,
  og_image VARCHAR(255) DEFAULT NULL,
  robots VARCHAR(40) NOT NULL DEFAULT 'index,follow',
  status ENUM('published','draft') NOT NULL DEFAULT 'published',
  sort INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  page_id INT NOT NULL,
  type VARCHAR(40) NOT NULL DEFAULT 'richtext',
  sort INT NOT NULL DEFAULT 0,
  visible TINYINT(1) NOT NULL DEFAULT 1,
  heading VARCHAR(255) DEFAULT NULL,
  subheading VARCHAR(255) DEFAULT NULL,
  body MEDIUMTEXT DEFAULT NULL,
  image VARCHAR(255) DEFAULT NULL,
  button_text VARCHAR(120) DEFAULT NULL,
  button_url VARCHAR(255) DEFAULT NULL,
  extra MEDIUMTEXT DEFAULT NULL,
  CONSTRAINT fk_sections_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(180) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(160) DEFAULT NULL,
  excerpt TEXT DEFAULT NULL,
  body MEDIUMTEXT DEFAULT NULL,
  banner VARCHAR(255) DEFAULT NULL,
  featured TINYINT(1) NOT NULL DEFAULT 0,
  meta_title VARCHAR(255) DEFAULT NULL,
  meta_description VARCHAR(320) DEFAULT NULL,
  status ENUM('published','draft') NOT NULL DEFAULT 'published',
  published_at DATE DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE media (
  id INT AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  path VARCHAR(255) NOT NULL,
  alt VARCHAR(255) DEFAULT NULL,
  size INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  skey VARCHAR(80) PRIMARY KEY,
  svalue TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Global settings (feed the shared header/footer) ───
INSERT INTO settings (skey, svalue) VALUES
 ('site_name','New Tampa Piano & Pedagogy Academy'),
 ('tagline','Piano Lessons for Students of All Ages'),
 ('phone','(813) 994-2452'),
 ('email','admin@NewTampaPPA.com'),
 ('address','10691 Cross Creek Blvd, Tampa, FL 33647'),
 ('facebook','https://www.facebook.com/NewTampaPianoAndPedagogyAcademy'),
 ('youtube','https://www.youtube.com/channel/UCKks-ZU-jFzRdAet5u_9_Ww'),
 ('base_url','');

-- ─── Pages (hero + SEO for every page) ───
INSERT INTO pages (slug,title,nav_label,hero_eyebrow,hero_heading,hero_subtext,hero_image,meta_title,meta_description,meta_keywords,canonical,robots,sort) VALUES
 ('home','Home','Home','Piano Lessons for All Ages','Where Music Begins','Professional piano education in New Tampa since 2011, led by Carnegie Hall performer Dr. Judith Jain.','images/hero-1.jpg','Piano Lessons in New Tampa | NTPPA Academy','Award-winning piano lessons for children & adults in New Tampa, FL. Led by Carnegie Hall performer Dr. Judith Jain. Book a free interview today.','piano lessons new tampa, piano academy tampa, kids piano lessons','https://www.newtampappa.com/','index,follow',1),
 ('programs','Programs','Programs','Our Curriculum','Programs for Every Age & Level','From preparatory classes for young children to adult programs — a structured path for every student.','images/prep-banner.jpg','Piano Programs | Preparatory, Core & Adult | NTPPA','Explore NTPPA piano programs: preparatory, core, and adult tracks, plus performance and summer camps in New Tampa, FL.','piano programs tampa, adult piano lessons, preparatory piano','https://www.newtampappa.com/programs','index,follow',2),
 ('about','About Us','About Us','Our Story','About New Tampa Piano & Pedagogy Academy','Meet Dr. Judith Jain and a faculty of degree-holding instructors dedicated to musical excellence.','images/about-banner.jpg','About NTPPA | Faculty & Philosophy | Tampa Piano','Learn about NTPPA — our founder Dr. Judith Jain, teaching philosophy, and degree-holding faculty in New Tampa, FL.','about ntppa, judith jain, piano faculty tampa','https://www.newtampappa.com/about','index,follow',3),
 ('gallery','Gallery','Gallery','Moments','Gallery','Recitals, performances, and studio moments from our students and faculty.','images/gallery-banner.jpg','Gallery | Recitals & Performances | NTPPA Tampa','Photos and videos from NTPPA recitals, performances, and studio events in New Tampa, FL.','ntppa gallery, piano recital photos tampa','https://www.newtampappa.com/gallery','index,follow',4),
 ('press','Press','Press','In the News','Press & Recognition','Awards, features, and community recognition for NTPPA and its students.','images/press-banner.jpg','Press & Awards | NTPPA in the News | Tampa','Press coverage, awards, and recognition for New Tampa Piano & Pedagogy Academy.','ntppa press, piano academy awards tampa','https://www.newtampappa.com/press','index,follow',5),
 ('blogs','Blogs','Blogs','Insights & Articles','The NTPPA Blog','Thoughts on piano education, child development, adult learning, and the transformative power of music.','images/blog-banner.jpg','Piano Blog | Tips, News & Insights | NTPPA Tampa','Read piano tips, insights, and educational articles from Dr. Judith Jain and the NTPPA faculty.','piano blog tampa, piano tips, learning piano','https://www.newtampappa.com/blogs','index,follow',6),
 ('career','Career','Career','Join Us','Careers at NTPPA','We are always looking for passionate, degree-holding piano instructors to join our faculty.','images/hero-4.jpg','Careers | Piano Teaching Jobs | NTPPA Tampa','Piano teaching careers at New Tampa Piano & Pedagogy Academy. Join our faculty of degree-holding instructors.','piano teaching jobs tampa, ntppa career','https://www.newtampappa.com/career','index,follow',7),
 ('contact','Contact','Contact','Get in Touch','Request a Free Interview','Start your piano journey — book a free conversation with Dr. Jain.','images/hero-2.jpg','Contact NTPPA | Book a Free Interview | Tampa','Contact New Tampa Piano & Pedagogy Academy. Book a free interview, get directions, and studio hours.','contact ntppa, piano lessons tampa contact','https://www.newtampappa.com/contact','index,follow',8);

-- ─── A few starter sections (client edits/adds the rest visually) ───
INSERT INTO sections (page_id,type,sort,visible,heading,subheading,body,image,button_text,button_url,extra)
 SELECT id,'richtext',1,1,'Why Families Choose NTPPA',NULL,'<p>Since 2011, New Tampa Piano &amp; Pedagogy Academy has provided structured, joyful piano education for students of all ages. Our faculty holds advanced degrees and our students perform, compete, and grow in confidence.</p>',NULL,NULL,NULL,NULL FROM pages WHERE slug='home';
INSERT INTO sections (page_id,type,sort,visible,heading,subheading,body,image,button_text,button_url,extra)
 SELECT id,'cards',2,1,'Our Programs','Preparatory · Core · Adult',NULL,NULL,NULL,NULL,'[{"title":"Preparatory","text":"Music foundations for young children.","image":"images/prep-banner.jpg"},{"title":"Core Program","text":"Structured lessons, theory, and performance.","image":"images/hero-3.jpg"},{"title":"Adult Program","text":"Beginners and returning adults, at your pace.","image":"images/hero-5.jpg"}]' FROM pages WHERE slug='home';
INSERT INTO sections (page_id,type,sort,visible,heading,subheading,body,image,button_text,button_url,extra)
 SELECT id,'cta',3,1,'Ready to Start Your Piano Journey?',NULL,'The next step is a free conversation with Dr. Jain.',NULL,'Request Free Interview','/contact',NULL FROM pages WHERE slug='home';
INSERT INTO sections (page_id,type,sort,visible,heading,subheading,body,image,button_text,button_url,extra)
 SELECT id,'richtext',1,1,'Our Teaching Philosophy',NULL,'<p>We believe every student can experience the joy and discipline of music. Our curriculum balances technique, theory, repertoire, and performance.</p>',NULL,NULL,NULL,NULL FROM pages WHERE slug='about';

-- ─── Blog posts ───
INSERT INTO posts (slug,title,author,excerpt,body,banner,featured,published_at,meta_title,meta_description) VALUES
 ('teaching-piano-to-adults','Teaching Piano to Adults: What I Have Learned Over the Years','Arasay Lima','Learning piano as an adult is far more than acquiring a musical skill — it is a deeply personal decision that requires courage and commitment.','<p>Learning piano as an adult is far more than acquiring a musical skill. It is a deeply personal decision that requires courage, commitment, and a willingness to reconnect with oneself.</p>','https://newtampappa.com/uploads/blogs/main/ChatGPT_Image_May_16,_2026,_05_19_17_PM.png',1,'2026-05-16','Teaching Piano to Adults | NTPPA','What I have learned teaching piano to adults — courage, time, and well-being.'),
 ('worth-the-investment','Is My Child''s Piano Education Worth the Investment?','Dr. Judith Jain','Signs that piano education is producing real value beyond the lesson itself.','<p>Real value shows up beyond the lesson itself: engagement, focus, discipline, resilience, and growing self-confidence.</p>','https://newtampappa.com/uploads/blogs/main/ChatGPT_Image_May_6,_2026,_11_04_50_AM.png',0,'2026-05-06',NULL,NULL),
 ('benefits-adult','The Benefits of Learning Piano as an Adult',NULL,'Adult piano learning is accessible and beneficial at any age.','<p>It is never too late — adult study supports cognition, memory, and self-expression at any age.</p>','https://newtampappa.com/uploads/blogs/main/ChatGPT_Image_May_6,_2026,_10_59_04_AM.png',0,'2026-03-06',NULL,NULL);
