-- Ask Manisha API - MySQL Database Schema
-- Database structure matching original MongoDB schema

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

-- Notifications table
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    userId INT NOT NULL,
    recipientId INT,
    isRead BOOLEAN DEFAULT FALSE,
    readAt TIMESTAMP NULL,
    type ENUM('general', 'horoscope', 'announcement', 'promotion', 'alert', 'quote') DEFAULT 'general',
    metadata JSON,
    sentBy INT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recipientId) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (sentBy) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (userId, isRead, createdAt),
    INDEX idx_recipient (recipientId, isRead, createdAt),
    INDEX idx_sent_by (sentBy, createdAt),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Feedback table
CREATE TABLE IF NOT EXISTS feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId INT NOT NULL,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    message TEXT NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_feedback (userId),
    INDEX idx_rating (rating),
    INDEX idx_created (createdAt DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Quotes table
CREATE TABLE IF NOT EXISTS quotes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description TEXT NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_created (createdAt DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Samvatinfo table
CREATE TABLE IF NOT EXISTS samvatinfo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sakaSalivahanaNumber INT NOT NULL,
    sakaSalivahanaNameNumber INT NOT NULL,
    sakaSalivahanaYearName VARCHAR(255) NOT NULL,
    vikramChaitradiNumber INT NOT NULL,
    vikramChaitradiNameNumber INT NOT NULL,
    vikramChaitradiYearName VARCHAR(255) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_created (createdAt DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Horoscopes table
CREATE TABLE IF NOT EXISTS horoscopes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sunsign ENUM('Aries', 'Taurus', 'Gemini', 'Cancer', 'Leo', 'Virgo', 'Libra', 'Scorpio', 'Sagittarius', 'Capricorn', 'Aquarius', 'Pisces') NOT NULL,
    period ENUM('daily', 'weekly', 'monthly', 'yearly') NOT NULL,
    title VARCHAR(255),
    content TEXT NOT NULL,
    luckyNumber VARCHAR(100),
    luckyColor VARCHAR(100),
    mood VARCHAR(100),
    keywords TEXT,
    intensity VARCHAR(50),
    date DATE,
    isActive BOOLEAN DEFAULT TRUE,
    deletedAt TIMESTAMP NULL,
    createdBy INT,
    updatedBy INT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (createdBy) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updatedBy) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_sunsign_period (sunsign, period, isActive, createdAt),
    INDEX idx_active (isActive, deletedAt),
    INDEX idx_date (date DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
