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

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'admin',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS announcers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  photo VARCHAR(255) NULL,
  bio TEXT NULL,
  program_name VARCHAR(120) NULL,
  instagram VARCHAR(255) NULL,
  facebook VARCHAR(255) NULL,
  youtube VARCHAR(255) NULL,
  tiktok VARCHAR(255) NULL
);
CREATE TABLE IF NOT EXISTS programs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  description TEXT NULL,
  image VARCHAR(255) NULL
);
CREATE TABLE IF NOT EXISTS schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  day_of_week TINYINT NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  program_id INT NOT NULL,
  announcer_id INT NULL,
  FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE,
  FOREIGN KEY (announcer_id) REFERENCES announcers(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
CREATE TABLE IF NOT EXISTS news (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NULL,
  title VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL UNIQUE,
  excerpt TEXT NULL,
  content LONGTEXT NULL,
  cover_image VARCHAR(255) NULL,
  published_at DATETIME NOT NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS podcasts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL UNIQUE,
  description TEXT NULL,
  audio_url VARCHAR(255) NOT NULL,
  cover_image VARCHAR(255) NULL,
  published_at DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  cover_image VARCHAR(255) NULL,
  event_date DATE NOT NULL,
  location VARCHAR(150) NULL
);
CREATE TABLE IF NOT EXISTS polls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  question VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS poll_options (
  id INT AUTO_INCREMENT PRIMARY KEY,
  poll_id INT NOT NULL,
  option_text VARCHAR(160) NOT NULL,
  votes INT NOT NULL DEFAULT 0,
  FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS banners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  image_url VARCHAR(255) NOT NULL,
  link_url VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  clicks INT NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS contact_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL,
  whatsapp VARCHAR(50) NULL,
  subject VARCHAR(160) NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS music_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  whatsapp VARCHAR(50) NULL,
  music VARCHAR(150) NOT NULL,
  artist VARCHAR(150) NOT NULL,
  dedication VARCHAR(255) NULL,
  message TEXT NULL,
  created_at DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(160) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL
);

INSERT INTO users (name,email,password) VALUES ('Administrador','admin@radio.local','$2b$12$/QnKclgstgYYOyec.//HN.710ycSY94jZsUhdJQmPB9xPBJPsx4uK');
INSERT INTO announcers (name,photo,bio,program_name) VALUES
('Larissa Costa','https://placehold.co/320x320/1f2937/ffffff?text=Larissa','Apaixonada por música pop e interação ao vivo.','Morning Hits'),
('Pedro Alves','https://placehold.co/320x320/0f172a/ffffff?text=Pedro','Entretenimento e promoções durante a tarde.','Tarde Mix'),
('André Lima','https://placehold.co/320x320/334155/ffffff?text=André','Informação e energia para abrir a programação.','Bom Dia FM');
INSERT INTO programs (title,description,image) VALUES
('Bom Dia FM','Jornal, prestação de serviço e boa música.','https://placehold.co/800x450/334155/ffffff?text=Bom+Dia+FM'),
('Morning Hits','Hits e interação ao vivo.','https://placehold.co/800x450/1f2937/ffffff?text=Morning+Hits'),
('Tarde Mix','Entretenimento, promoções e clássicos.','https://placehold.co/800x450/0f172a/ffffff?text=Tarde+Mix');
INSERT INTO schedules (day_of_week,start_time,end_time,program_id,announcer_id) VALUES
(1,'06:00:00','08:00:00',1,3),(1,'08:00:00','12:00:00',2,1),(1,'12:00:00','18:00:00',3,2),
(2,'06:00:00','08:00:00',1,3),(2,'08:00:00','12:00:00',2,1),(2,'12:00:00','18:00:00',3,2),
(3,'06:00:00','08:00:00',1,3),(3,'08:00:00','12:00:00',2,1),(3,'12:00:00','18:00:00',3,2),
(4,'06:00:00','08:00:00',1,3),(4,'08:00:00','12:00:00',2,1),(4,'12:00:00','18:00:00',3,2),
(5,'06:00:00','08:00:00',1,3),(5,'08:00:00','12:00:00',2,1),(5,'12:00:00','18:00:00',3,2);
INSERT INTO categories (name) VALUES ('Eventos'),('Promoções'),('Podcast');
INSERT INTO news (category_id,title,slug,excerpt,content,cover_image,published_at) VALUES
(1,'Festival de inverno movimenta a cidade','festival-de-inverno','Cobertura especial com entrevistas e bastidores.','Conteúdo completo da notícia dentro do próprio site.','https://placehold.co/800x500/1e293b/ffffff?text=Notícia+1',NOW()),
(2,'Nova promoção vale ingressos VIP','nova-promocao','Participe ao vivo e concorra em nossas redes.','Detalhes da promoção com regulamento.','https://placehold.co/800x500/334155/ffffff?text=Notícia+2',NOW()),
(3,'Podcast especial com artista local','podcast-especial','Episódio exclusivo já disponível no portal.','Escute o episódio completo na área de podcasts.','https://placehold.co/800x500/0f172a/ffffff?text=Notícia+3',NOW());
INSERT INTO podcasts (title,slug,description,audio_url,cover_image,published_at) VALUES
('Entrevista da Semana','entrevista-da-semana','Conversa exclusiva com artista convidado.','https://www.soundhelix.com/examples/mp3/SoundHelix-Song-1.mp3','https://placehold.co/600x600/111827/ffffff?text=Podcast+1',NOW()),
('Resumo Musical','resumo-musical','Os lançamentos que dominaram a semana.','https://www.soundhelix.com/examples/mp3/SoundHelix-Song-2.mp3','https://placehold.co/600x600/ffffff/111827?text=Podcast+2',NOW());
INSERT INTO events (title,description,cover_image,event_date,location) VALUES
('Show ao Vivo na Praça','Cobertura especial direto do evento.','https://placehold.co/900x500/1f2937/ffffff?text=Evento+1',DATE_ADD(CURDATE(), INTERVAL 7 DAY),'Praça Central'),
('Sorteio de Camarote','Promoção exclusiva para ouvintes.','https://placehold.co/900x500/334155/ffffff?text=Evento+2',DATE_ADD(CURDATE(), INTERVAL 12 DAY),'Estúdio FM');
INSERT INTO polls (question,is_active) VALUES ('Qual música você gostaria de ouvir hoje?',1);
INSERT INTO poll_options (poll_id,option_text,votes) VALUES (1,'Pop Internacional',8),(1,'Sertanejo',5),(1,'Flashback 2000',4);
INSERT INTO banners (title,image_url,link_url,is_active) VALUES ('Baixe nosso app','https://placehold.co/1200x300/111827/ffffff?text=Banner+Rádio+FM','#',1);