-- Kirtan Tarang - Database Schema
-- Database name: kirtan_manch

CREATE DATABASE IF NOT EXISTS kirtan_manch;
USE kirtan_manch;

-- 1. Kirtankars Table
CREATE TABLE IF NOT EXISTS kirtankars (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255),
    style ENUM('Varkari', 'Naradiya', 'Haridasi', 'Rashtriya') NOT NULL,
    region VARCHAR(255),
    experience INT,
    rating DECIMAL(3,1) DEFAULT 5.0,
    charge_per_session INT,
    youtube_url VARCHAR(255),
    bio TEXT,
    status ENUM('Pending', 'Approved') DEFAULT 'Pending',
    avatar_emoji VARCHAR(10) DEFAULT '🙏'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Bookings Table
CREATE TABLE IF NOT EXISTS bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kirtankar_id INT NOT NULL,
    organizer_name VARCHAR(255) NOT NULL,
    organizer_phone VARCHAR(20) NOT NULL,
    event_date DATE NOT NULL,
    event_time VARCHAR(20) NOT NULL,
    event_venue VARCHAR(255) NOT NULL,
    status ENUM('pending', 'confirmed', 'delivered', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (kirtankar_id) REFERENCES kirtankars(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. News Table
CREATE TABLE IF NOT EXISTS news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date_badge VARCHAR(50),
    title_mr VARCHAR(255) NOT NULL,
    title_en VARCHAR(255) NOT NULL,
    desc_mr TEXT,
    desc_en TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Videos Table
CREATE TABLE IF NOT EXISTS videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    speaker VARCHAR(255) NOT NULL,
    duration VARCHAR(20),
    youtube_url VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- SEED DATA
-- --------------------------------------------------------

-- Insert Pre-seeded Kirtankars
INSERT INTO kirtankars (name, phone, email, style, region, experience, rating, charge_per_session, youtube_url, bio, status, avatar_emoji)
VALUES 
('H.B.P. Indurikar Maharaj', '9823012345', 'indurikar@example.com', 'Varkari', 'Sangamner, Ahmednagar', 28, 4.9, 12000, 'https://www.youtube.com/watch?v=indurikar', 'Famous for his humorous and reformative social Kirtans that target modern societal issues.', 'Approved', '🙏'),
('H.B.P. Ramrao Maharaj Dhok', '9812345678', 'ramrao@example.com', 'Varkari', 'Nagpur', 32, 5.0, 15000, 'https://www.youtube.com/watch?v=dhok_maharaj', 'A highly respected scholar specializing in Dnyaneshwari, Tukaram Gatha, and traditional Varkari commentary.', 'Approved', '📿'),
('H.B.P. Baba Maharaj Satarkar', '9855566677', 'satarkar@example.com', 'Varkari', 'Satara / Mumbai', 50, 4.8, 20000, 'https://www.youtube.com/watch?v=baba_maharaj', 'A legendary Kirtankar known for his highly melodious voice, classic Varkari style, and global spiritual discourse.', 'Approved', '🪕'),
('H.B.P. Dehukar Maharaj', '9877788899', 'dehukar@example.com', 'Varkari', 'Dehu, Pune', 18, 4.7, 9000, 'https://www.youtube.com/watch?v=dehukar', 'Direct descendant of Sant Tukaram Maharaj carrying forward the pure ancestral tradition of Dehu Palkhi and Kirtan.', 'Approved', '🕉️'),
('H.B.P. Chaitanya Maharaj Kabnurkar', '9866655544', 'kabnurkar@example.com', 'Naradiya', 'Kolhapur', 22, 4.6, 10000, 'https://www.youtube.com/watch?v=kabnurkar', 'Renowned for Naradiya (classical storytelling) style Kirtan, mixing historic events with rich spiritual lessons.', 'Approved', '🌸');

-- Insert Pre-seeded Bookings
INSERT INTO bookings (kirtankar_id, organizer_name, organizer_phone, event_date, event_time, event_venue, status)
VALUES
(1, 'Pandharpur Vitthal Temple Trust', '9822110044', '2026-07-23', '05:00 PM', 'Main Temple Hall, Pandharpur', 'confirmed'),
(2, 'Alandi Devachi Sansthan', '9822110044', '2026-11-20', '06:30 PM', 'Sant Dnyaneshwar Samadhi Ground, Alandi', 'pending'),
(3, 'Pune Devotional Foundation', '9811223344', '2026-05-30', '07:00 PM', 'Shaniwar Wada Ground, Pune', 'delivered');

-- Insert Pre-seeded News
INSERT INTO news (date_badge, title_mr, title_en, desc_mr, desc_en)
VALUES
('18 May 2026', 'आषाढी एकादशी सोहळा २०२६ चे नियोजन सुरू', 'Planning Commences for Ashadhi Ekadashi Festival 2026', 'पंढरपूर मंदिर समितीने आषाढी वारीच्या नियोजनाची पहिली बैठक आयोजित केली. लाखो भाविकांच्या सुरक्षेसाठी आणि निवाऱ्यासाठी विशेष व्यवस्था केली जाईल.', 'The Pandharpur Temple Committee organized the first planning meeting for Ashadhi Wari. Special facilities will be established for security and accommodation.'),
('15 May 2026', 'संत ज्ञानेश्वर महाराज पालखी प्रस्थान तारीख घोषित', 'Sant Dnyaneshwar Maharaj Palkhi Departure Date Announced', 'श्री संत ज्ञानेश्वर महाराज संस्थानाने आळंदी येथून आषाढी वारीसाठी निघणाऱ्या पालखी सोहळ्याचे प्रस्थान वेळापत्रक जाहीर केले आहे.', 'The Shri Sant Dnyaneshwar Maharaj Sansthan has declared the departure schedule for the Palkhi Sohala leaving Alandi for Ashadhi Wari.'),
('10 May 2026', 'कीर्तन तरंग ॲपचे भव्य उद्घाटन', 'Grand Launch of Kirtan Tarang App', 'महाराष्ट्रभरातील कीर्तनकार आणि भाविकांना डिजिटल प्लॅटफॉर्मवर जोडण्यासाठी "कीर्तन तरंग" मंचाचे भव्य अनावरण करण्यात आले.', 'The digital platform "Kirtan Tarang" was grandly unveiled to connect Kirtankars and devotees across Maharashtra.');

-- Insert Pre-seeded Videos
INSERT INTO videos (title, speaker, duration, youtube_url)
VALUES
('ज्ञानेश्वरी निरूपण - अध्यात्म आणि व्यवहार', 'H.B.P. Ramrao Maharaj Dhok', '1:12:45', 'https://www.youtube.com/watch?v=example_dhok_1'),
('तरुणांचे प्रबोधन आणि उत्कृष्ट विनोद कीर्तन', 'H.B.P. Indurikar Maharaj', '45:30', 'https://www.youtube.com/watch?v=example_indu_1'),
('हरिपाठ चिंतन आणि सुंदर भजन संग्रह', 'H.B.P. Baba Maharaj Satarkar', '1:30:15', 'https://www.youtube.com/watch?v=example_baba_1');