-- ====================================
-- ESANG DELICACIES DATABASE INITIALIZATION
-- ====================================

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS ESANG_DB;
USE ESANG_DB;

-- ====================================
-- NOTIFICATIONS
-- ====================================
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    user_type ENUM('customer', 'admin', 'cashier', 'rider', 'order_manager') NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('order_placed', 'order_approved', 'order_preparing', 'order_ready', 'order_delivered', 'order_completed', 'permission_request', 'general') NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    related_order_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_notifications (user_id, user_type),
    INDEX idx_unread (is_read),
    INDEX idx_type (type)
);

-- ====================================
-- MENU/PRODUCTS (Enhanced)
-- ====================================
CREATE TABLE IF NOT EXISTS PRODUCT (
    prodId INT AUTO_INCREMENT PRIMARY KEY,
    prodName VARCHAR(100) NOT NULL,
    prodCategory VARCHAR(100) NOT NULL,
    prodPrice DECIMAL(10, 2) NOT NULL,
    prodDescription TEXT,
    prodImage VARCHAR(255),
    is_available BOOLEAN DEFAULT TRUE,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (prodCategory),
    INDEX idx_available (is_available)
);

-- Rest of the original schema continues...
-- (Including ORDERS, ORDER_DETAILS, PAYMENTS, REVIEW, DELIVERY, RECEIPTS, RETURN_ORDER, etc.)
-- I'll include the complete schema from the user's provided SQL

-- ====================================
-- SERVICES
-- ====================================
CREATE TABLE IF NOT EXISTS ORDERS (
    orderId INT AUTO_INCREMENT PRIMARY KEY,
    customerId INT NOT NULL,
    orderManagerId INT NOT NULL,
    orderDate DATE NOT NULL,
    deliveryAddress TEXT NOT NULL,
    totalAmount DECIMAL(10, 2) NOT NULL,
    orderStatus VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customerId) REFERENCES CUSTOMER(customerId),
    FOREIGN KEY (orderManagerId) REFERENCES ORDER_MANAGER(empId)
);

CREATE TABLE IF NOT EXISTS ORDER_DETAILS (
    orderDetailId INT AUTO_INCREMENT PRIMARY KEY,
    orderId INT NOT NULL,
    prodId INT NOT NULL,
    cashierId INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    amount DECIMAL(10, 2) NOT NULL,
    paymentMethod VARCHAR(50),
    FOREIGN KEY (orderId) REFERENCES ORDERS(orderId),
    FOREIGN KEY (prodId) REFERENCES PRODUCT(prodId),
    FOREIGN KEY (cashierId) REFERENCES CASHIER(empId)
);

CREATE TABLE IF NOT EXISTS PAYMENTS (
    paymentId INT AUTO_INCREMENT PRIMARY KEY,
    orderId INT NOT NULL,
    cashierId INT NOT NULL,
    paymentDate DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL CHECK (amount <= 1000),
    paymentMethod VARCHAR(50),
    FOREIGN KEY (orderId) REFERENCES ORDERS(orderId),
    FOREIGN KEY (cashierId) REFERENCES CASHIER(empId)
);

CREATE TABLE IF NOT EXISTS REVIEW (
    reviewId INT AUTO_INCREMENT PRIMARY KEY,
    customerId INT NOT NULL,
    orderId INT,
    rating VARCHAR(10),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customerId) REFERENCES CUSTOMER(customerId)
);

CREATE TABLE IF NOT EXISTS DELIVERY (
    delId INT AUTO_INCREMENT PRIMARY KEY,
    riderId INT NOT NULL,
    orderManagerId INT NOT NULL,
    orderId INT NOT NULL,
    delStatus VARCHAR(50),
    plateNum VARCHAR(15) NOT NULL,
    delTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (riderId) REFERENCES RIDER(empId),
    FOREIGN KEY (orderManagerId) REFERENCES ORDER_MANAGER(empId),
    FOREIGN KEY (orderId) REFERENCES ORDERS(orderId)
);

CREATE TABLE IF NOT EXISTS RECEIPTS (
    receiptId INT AUTO_INCREMENT PRIMARY KEY,
    orderId INT NOT NULL,
    cashierId INT NOT NULL,
    fileName VARCHAR(255) NOT NULL,
    fileType VARCHAR(50) DEFAULT 'application/pdf',
    fileData LONGBLOB NOT NULL,
    uploadDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (orderId) REFERENCES ORDERS(orderId),
    FOREIGN KEY (cashierId) REFERENCES CASHIER(empId)
);

-- ====================================
-- RETURN ORDERS
-- ====================================
CREATE TABLE IF NOT EXISTS RETURN_ORDER (
    returnId INT AUTO_INCREMENT PRIMARY KEY,
    orderId INT NOT NULL,
    customerId INT NOT NULL,
    returnDate DATE NOT NULL DEFAULT (CURRENT_DATE),
    reason TEXT NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending',
    refundAmount DECIMAL(10, 2) DEFAULT 0.00,
    FOREIGN KEY (orderId) REFERENCES ORDERS(orderId),
    FOREIGN KEY (customerId) REFERENCES CUSTOMER(customerId)
);

CREATE TABLE IF NOT EXISTS RETURN_ORDER_DETAILS (
    returnDetailId INT AUTO_INCREMENT PRIMARY KEY,
    returnId INT NOT NULL,
    prodId INT NOT NULL,
    quantity INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (returnId) REFERENCES RETURN_ORDER(returnId),
    FOREIGN KEY (prodId) REFERENCES PRODUCT(prodId)
);
