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

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS alerts;
DROP TABLE IF EXISTS attendance_records;
DROP TABLE IF EXISTS event_documents;
DROP TABLE IF EXISTS event_type_required_documents;
DROP TABLE IF EXISTS document_types;
DROP TABLE IF EXISTS event_funding;
DROP TABLE IF EXISTS event_expenses;
DROP TABLE IF EXISTS event_menus;
DROP TABLE IF EXISTS item_price_history;
DROP TABLE IF EXISTS recipe_ingredients;
DROP TABLE IF EXISTS recipes;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS event_dates;
DROP TABLE IF EXISTS event_meal_selections;
DROP TABLE IF EXISTS event_faculty_assignments;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS event_types;
DROP TABLE IF EXISTS guest_master;
DROP TABLE IF EXISTS recipient_master;
DROP TABLE IF EXISTS faculty_profiles;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    email VARCHAR(150) NULL,
    contact_no VARCHAR(50) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE faculty_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    employee_no VARCHAR(50) NOT NULL UNIQUE,
    department VARCHAR(150) NOT NULL,
    designation VARCHAR(150) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_faculty_profiles_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE recipient_master (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    recipient_no VARCHAR(50) NULL UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NOT NULL,
    extension_name VARCHAR(30) NULL,
    category ENUM('faculty','non_teaching','student') NOT NULL,
    department_or_section VARCHAR(150) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE guest_master (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NOT NULL,
    extension_name VARCHAR(30) NULL,
    organization VARCHAR(150) NULL,
    remarks TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE event_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type_name VARCHAR(150) NOT NULL UNIQUE,
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    event_type_id INT UNSIGNED NOT NULL,
    description TEXT NULL,
    venue VARCHAR(255) NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    pax_count INT UNSIGNED NOT NULL DEFAULT 0,
    attendance_mode ENUM('AM','PM','BOTH') NOT NULL DEFAULT 'BOTH',
    funding_mode ENUM('cash_advance','reimbursement','mixed') NOT NULL DEFAULT 'cash_advance',
    requester_user_id INT UNSIGNED NOT NULL,
    status ENUM('draft','active','for_liquidation','completed','closed') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_events_event_type FOREIGN KEY (event_type_id) REFERENCES event_types(id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_events_requester FOREIGN KEY (requester_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_event_dates CHECK (end_date >= start_date)
) ENGINE=InnoDB;

CREATE TABLE event_faculty_assignments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    role_label ENUM('requester','preparer') NOT NULL DEFAULT 'preparer',
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_faculty (event_id, user_id, role_label),
    CONSTRAINT fk_event_faculty_assignments_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_event_faculty_assignments_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE event_meal_selections (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    meal_code ENUM('breakfast','am_snack','lunch','pm_snack','dinner') NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_meal (event_id, meal_code),
    CONSTRAINT fk_event_meal_selections_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE event_dates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    event_date DATE NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_date (event_id, event_date),
    CONSTRAINT fk_event_dates_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(150) NOT NULL,
    category VARCHAR(100) NOT NULL,
    unit_of_measure VARCHAR(50) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_items_name_unit (item_name, unit_of_measure)
) ENGINE=InnoDB;

CREATE TABLE menu_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_name VARCHAR(150) NOT NULL UNIQUE,
    category VARCHAR(100) NULL,
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE recipes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_item_id INT UNSIGNED NOT NULL,
    standard_yield DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    serving_basis VARCHAR(100) NOT NULL DEFAULT 'persons',
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_recipe_menu_item (menu_item_id),
    CONSTRAINT fk_recipes_menu_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE recipe_ingredients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    recipe_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
    unit VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_recipe_item (recipe_id, item_id, unit),
    CONSTRAINT fk_recipe_ingredients_recipe FOREIGN KEY (recipe_id) REFERENCES recipes(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_recipe_ingredients_item FOREIGN KEY (item_id) REFERENCES items(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE item_price_history (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED NOT NULL,
    reference_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    price_date DATE NOT NULL,
    remarks TEXT NULL,
    encoded_by INT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_item_price_lookup (item_id, price_date),
    CONSTRAINT fk_item_price_history_item FOREIGN KEY (item_id) REFERENCES items(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_item_price_history_user FOREIGN KEY (encoded_by) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE event_menus (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    meal_code ENUM('breakfast','am_snack','lunch','pm_snack','dinner') NOT NULL,
    menu_item_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_menu (event_id, meal_code, menu_item_id),
    CONSTRAINT fk_event_menus_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_event_menus_menu_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE event_expenses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NULL,
    expense_item_name VARCHAR(150) NULL,
    quantity DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
    unit VARCHAR(50) NOT NULL,
    unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    vendor_name VARCHAR(150) NULL,
    receipt_no VARCHAR(100) NULL,
    purchase_date DATE NOT NULL,
    remarks TEXT NULL,
    created_by INT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_event_expenses_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_event_expenses_item FOREIGN KEY (item_id) REFERENCES items(id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_event_expenses_user FOREIGN KEY (created_by) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE event_funding (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    cash_advance_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    reimbursement_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    refund_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_funding_event (event_id),
    CONSTRAINT fk_event_funding_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE document_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_name VARCHAR(150) NOT NULL UNIQUE,
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE event_type_required_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type_id INT UNSIGNED NOT NULL,
    document_type_id INT UNSIGNED NOT NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_type_document (event_type_id, document_type_id),
    CONSTRAINT fk_event_type_required_documents_event_type FOREIGN KEY (event_type_id) REFERENCES event_types(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_event_type_required_documents_document_type FOREIGN KEY (document_type_id) REFERENCES document_types(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE event_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    document_type_id INT UNSIGNED NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(30) NULL,
    file_size BIGINT UNSIGNED NULL,
    uploaded_by INT UNSIGNED NOT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remarks TEXT NULL,
    CONSTRAINT fk_event_documents_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_event_documents_document_type FOREIGN KEY (document_type_id) REFERENCES document_types(id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_event_documents_user FOREIGN KEY (uploaded_by) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE attendance_records (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    event_date DATE NOT NULL,
    session_code ENUM('AM','PM') NOT NULL,
    recipient_type ENUM('recipient','guest') NOT NULL,
    recipient_id INT UNSIGNED NOT NULL,
    attendance_status ENUM('present','absent') NOT NULL DEFAULT 'present',
    recorded_by INT UNSIGNED NOT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_attendance_unique (event_id, event_date, session_code, recipient_type, recipient_id),
    INDEX idx_attendance_event_date (event_id, event_date, session_code),
    CONSTRAINT fk_attendance_records_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_attendance_records_user FOREIGN KEY (recorded_by) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE alerts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    alert_type ENUM('missing_document','incomplete_liquidation','attendance_pending') NOT NULL,
    message VARCHAR(255) NOT NULL,
    status ENUM('open','resolved') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_alerts_event FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO roles (role_name) VALUES
('Admin'),
('Faculty Requester'),
('Faculty Preparer');

INSERT INTO users (full_name, username, password_hash, role_id, email, contact_no, status) VALUES
('System Administrator', 'admin', '$2y$12$dBO6amz5mfd46fuZXBgH0u5U8W6AC166pHOBssDcPop16axC/Jxiq', 1, 'admin@example.com', '09170000001', 'active'),
('Prof. Maria Santos', 'maria.santos', '$2y$12$dBO6amz5mfd46fuZXBgH0u5U8W6AC166pHOBssDcPop16axC/Jxiq', 2, 'maria@example.com', '09170000002', 'active'),
('Prof. Jose Rivera', 'jose.rivera', '$2y$12$dBO6amz5mfd46fuZXBgH0u5U8W6AC166pHOBssDcPop16axC/Jxiq', 3, 'jose@example.com', '09170000003', 'active'),
('Prof. Ana Cruz', 'ana.cruz', '$2y$12$dBO6amz5mfd46fuZXBgH0u5U8W6AC166pHOBssDcPop16axC/Jxiq', 3, 'ana@example.com', '09170000004', 'active');

INSERT INTO faculty_profiles (user_id, employee_no, department, designation) VALUES
(2, 'EMP-1001', 'Food Technology', 'Faculty Requester'),
(3, 'EMP-1002', 'Food Technology', 'Faculty Preparer'),
(4, 'EMP-1003', 'Food Technology', 'Faculty Preparer');

INSERT INTO event_types (event_type_name, description, status) VALUES
('Seminar', 'Seminar or training event', 'active'),
('Meeting', 'Official meeting or conference', 'active'),
('Extension Activity', 'University extension activity', 'active'),
('Special Program', 'Special university program or event', 'active');

INSERT INTO document_types (document_name, description, status) VALUES
('Official Receipt', 'Official receipt for purchased goods or services', 'active'),
('Invoice', 'Invoice or billing statement', 'active'),
('Canvass', 'Canvass or market research document', 'active'),
('Attendance Sheet', 'Attendance support document', 'active'),
('Photos', 'Event or catering photos', 'active'),
('Menu', 'Approved or used menu', 'active'),
('Approval Memo', 'Event approval or authority document', 'active'),
('Refund Receipt', 'Proof of refund of unspent cash advance', 'active'),
('Other Supporting Document', 'Additional required file', 'active');

INSERT INTO event_type_required_documents (event_type_id, document_type_id, is_required)
SELECT et.id, dt.id, 1
FROM event_types et
JOIN document_types dt
WHERE et.event_type_name = 'Seminar'
  AND dt.document_name IN ('Official Receipt', 'Attendance Sheet', 'Menu', 'Photos', 'Approval Memo');



INSERT INTO recipient_master (recipient_no, first_name, middle_name, last_name, extension_name, category, department_or_section, status) VALUES
('FAC-2001', 'Juan', 'L.', 'Dela Cruz', NULL, 'faculty', 'Food Technology', 'active'),
('NT-3001', 'Liza', 'M.', 'Reyes', NULL, 'non_teaching', 'Administration', 'active'),
('ST-4001', 'Mark', 'T.', 'Gonzales', NULL, 'student', 'BS Food Technology', 'active'),
('ST-4002', 'Ana', 'P.', 'Lopez', NULL, 'student', 'BS Food Technology', 'active');

INSERT INTO guest_master (first_name, middle_name, last_name, extension_name, organization, remarks, status) VALUES
('Elena', NULL, 'Torres', NULL, 'Guest Speaker', 'External seminar speaker', 'active'),
('Robert', NULL, 'Mendoza', NULL, 'Industry Partner', 'Partner representative', 'active');

INSERT INTO items (item_name, category, unit_of_measure, status) VALUES
('Rice', 'Ingredient', 'kg', 'active'),
('Chicken', 'Ingredient', 'kg', 'active'),
('Soy Sauce', 'Ingredient', 'liter', 'active'),
('Cooking Oil', 'Ingredient', 'liter', 'active'),
('Garlic', 'Ingredient', 'kg', 'active'),
('Onion', 'Ingredient', 'kg', 'active'),
('Bread', 'Ingredient', 'pack', 'active'),
('Juice', 'Beverage', 'liter', 'active'),
('Disposable Spoon', 'Material', 'pack', 'active');

INSERT INTO menu_items (menu_name, category, description, status) VALUES
('Chicken Adobo', 'Main Dish', 'Classic Filipino chicken adobo', 'active'),
('Steamed Rice', 'Rice', 'Plain steamed rice', 'active'),
('Bread and Juice', 'Snack', 'Simple snack service', 'active');

INSERT INTO recipes (menu_item_id, standard_yield, serving_basis, notes)
SELECT id, CASE menu_name
    WHEN 'Chicken Adobo' THEN 25
    WHEN 'Steamed Rice' THEN 25
    WHEN 'Bread and Juice' THEN 25
    ELSE 25 END,
'persons',
'Demo recipe seed data'
FROM menu_items
WHERE menu_name IN ('Chicken Adobo', 'Steamed Rice', 'Bread and Juice');

INSERT INTO recipe_ingredients (recipe_id, item_id, quantity, unit)
SELECT r.id, i.id, 4.0000, 'kg'
FROM recipes r
JOIN menu_items m ON m.id = r.menu_item_id
JOIN items i ON i.item_name = 'Chicken'
WHERE m.menu_name = 'Chicken Adobo';

INSERT INTO recipe_ingredients (recipe_id, item_id, quantity, unit)
SELECT r.id, i.id, 0.5000, 'liter'
FROM recipes r
JOIN menu_items m ON m.id = r.menu_item_id
JOIN items i ON i.item_name = 'Soy Sauce'
WHERE m.menu_name = 'Chicken Adobo';

INSERT INTO recipe_ingredients (recipe_id, item_id, quantity, unit)
SELECT r.id, i.id, 0.2500, 'kg'
FROM recipes r
JOIN menu_items m ON m.id = r.menu_item_id
JOIN items i ON i.item_name = 'Garlic'
WHERE m.menu_name = 'Chicken Adobo';

INSERT INTO recipe_ingredients (recipe_id, item_id, quantity, unit)
SELECT r.id, i.id, 0.5000, 'kg'
FROM recipes r
JOIN menu_items m ON m.id = r.menu_item_id
JOIN items i ON i.item_name = 'Onion'
WHERE m.menu_name = 'Chicken Adobo';

INSERT INTO recipe_ingredients (recipe_id, item_id, quantity, unit)
SELECT r.id, i.id, 4.0000, 'kg'
FROM recipes r
JOIN menu_items m ON m.id = r.menu_item_id
JOIN items i ON i.item_name = 'Rice'
WHERE m.menu_name = 'Steamed Rice';

INSERT INTO recipe_ingredients (recipe_id, item_id, quantity, unit)
SELECT r.id, i.id, 25.0000, 'pack'
FROM recipes r
JOIN menu_items m ON m.id = r.menu_item_id
JOIN items i ON i.item_name = 'Bread'
WHERE m.menu_name = 'Bread and Juice';

INSERT INTO recipe_ingredients (recipe_id, item_id, quantity, unit)
SELECT r.id, i.id, 6.0000, 'liter'
FROM recipes r
JOIN menu_items m ON m.id = r.menu_item_id
JOIN items i ON i.item_name = 'Juice'
WHERE m.menu_name = 'Bread and Juice';

INSERT INTO item_price_history (item_id, reference_price, price_date, remarks, encoded_by)
SELECT id, CASE item_name
    WHEN 'Rice' THEN 58.00
    WHEN 'Chicken' THEN 210.00
    WHEN 'Soy Sauce' THEN 95.00
    WHEN 'Cooking Oil' THEN 120.00
    WHEN 'Garlic' THEN 180.00
    WHEN 'Onion' THEN 140.00
    WHEN 'Bread' THEN 85.00
    WHEN 'Juice' THEN 90.00
    WHEN 'Disposable Spoon' THEN 65.00
    ELSE 50.00 END,
CURRENT_DATE,
'Demo market price',
1
FROM items;

INSERT INTO events (event_code, title, event_type_id, description, venue, start_date, end_date, pax_count, attendance_mode, funding_mode, requester_user_id, status)
SELECT 'EVT-2026-001',
       'Food Technology Seminar Catering',
       et.id,
       'Demo seeded event for Phase 6 final package.',
       'University AVR',
       CURRENT_DATE,
       CURRENT_DATE + INTERVAL 1 DAY,
       50,
       'BOTH',
       'mixed',
       2,
       'active'
FROM event_types et
WHERE et.event_type_name = 'Seminar'
LIMIT 1;

INSERT INTO event_faculty_assignments (event_id, user_id, role_label, is_primary)
SELECT e.id, 2, 'requester', 1 FROM events e WHERE e.event_code = 'EVT-2026-001';
INSERT INTO event_faculty_assignments (event_id, user_id, role_label, is_primary)
SELECT e.id, 3, 'preparer', 1 FROM events e WHERE e.event_code = 'EVT-2026-001';
INSERT INTO event_faculty_assignments (event_id, user_id, role_label, is_primary)
SELECT e.id, 4, 'preparer', 0 FROM events e WHERE e.event_code = 'EVT-2026-001';

INSERT INTO event_meal_selections (event_id, meal_code)
SELECT e.id, 'breakfast' FROM events e WHERE e.event_code = 'EVT-2026-001';
INSERT INTO event_meal_selections (event_id, meal_code)
SELECT e.id, 'lunch' FROM events e WHERE e.event_code = 'EVT-2026-001';
INSERT INTO event_meal_selections (event_id, meal_code)
SELECT e.id, 'pm_snack' FROM events e WHERE e.event_code = 'EVT-2026-001';

INSERT INTO event_dates (event_id, event_date)
SELECT e.id, CURRENT_DATE FROM events e WHERE e.event_code = 'EVT-2026-001';
INSERT INTO event_dates (event_id, event_date)
SELECT e.id, CURRENT_DATE + INTERVAL 1 DAY FROM events e WHERE e.event_code = 'EVT-2026-001';

INSERT INTO event_menus (event_id, meal_code, menu_item_id)
SELECT e.id, 'lunch', m.id
FROM events e
JOIN menu_items m ON m.menu_name = 'Chicken Adobo'
WHERE e.event_code = 'EVT-2026-001';

INSERT INTO event_menus (event_id, meal_code, menu_item_id)
SELECT e.id, 'lunch', m.id
FROM events e
JOIN menu_items m ON m.menu_name = 'Steamed Rice'
WHERE e.event_code = 'EVT-2026-001';

INSERT INTO event_menus (event_id, meal_code, menu_item_id)
SELECT e.id, 'pm_snack', m.id
FROM events e
JOIN menu_items m ON m.menu_name = 'Bread and Juice'
WHERE e.event_code = 'EVT-2026-001';

INSERT INTO event_funding (event_id, cash_advance_amount, reimbursement_amount, refund_amount, notes)
SELECT e.id, 8000.00, 500.00, 0.00, 'Demo funding summary'
FROM events e WHERE e.event_code = 'EVT-2026-001';

INSERT INTO event_expenses (event_id, item_id, expense_item_name, quantity, unit, unit_cost, line_total, vendor_name, receipt_no, purchase_date, remarks, created_by)
SELECT e.id, i.id, NULL, 12.0000, 'kg', 58.00, 696.00, 'Local Market', 'OR-1001', CURRENT_DATE, 'Rice purchase', 3
FROM events e JOIN items i ON i.item_name = 'Rice'
WHERE e.event_code = 'EVT-2026-001';

INSERT INTO event_expenses (event_id, item_id, expense_item_name, quantity, unit, unit_cost, line_total, vendor_name, receipt_no, purchase_date, remarks, created_by)
SELECT e.id, i.id, NULL, 10.0000, 'kg', 210.00, 2100.00, 'Poultry Supplier', 'OR-1002', CURRENT_DATE, 'Chicken purchase', 3
FROM events e JOIN items i ON i.item_name = 'Chicken'
WHERE e.event_code = 'EVT-2026-001';

INSERT INTO attendance_records (event_id, event_date, session_code, recipient_type, recipient_id, attendance_status, recorded_by)
SELECT e.id, CURRENT_DATE, 'AM', 'recipient', r.id, 'present', 3
FROM events e, recipient_master r
WHERE e.event_code = 'EVT-2026-001' AND r.recipient_no IN ('FAC-2001', 'ST-4001');

INSERT INTO attendance_records (event_id, event_date, session_code, recipient_type, recipient_id, attendance_status, recorded_by)
SELECT e.id, CURRENT_DATE, 'PM', 'guest', g.id, 'present', 3
FROM events e, guest_master g
WHERE e.event_code = 'EVT-2026-001' AND g.last_name = 'Torres';

CREATE OR REPLACE VIEW vw_event_expense_summary AS
SELECT e.id AS event_id, e.event_code, e.title, COALESCE(SUM(ee.line_total), 0) AS total_actual_expenses
FROM events e
LEFT JOIN event_expenses ee ON ee.event_id = e.id
GROUP BY e.id, e.event_code, e.title;

CREATE OR REPLACE VIEW vw_event_document_status AS
SELECT e.id AS event_id,
       e.title,
       et.event_type_name,
       COUNT(CASE WHEN etrd.is_required = 1 THEN 1 END) AS required_documents,
       COUNT(CASE WHEN etrd.is_required = 1 AND ed.id IS NOT NULL THEN 1 END) AS uploaded_required_documents
FROM events e
JOIN event_types et ON et.id = e.event_type_id
LEFT JOIN event_type_required_documents etrd ON etrd.event_type_id = et.id AND etrd.is_required = 1
LEFT JOIN event_documents ed ON ed.event_id = e.id AND ed.document_type_id = etrd.document_type_id
GROUP BY e.id, e.title, et.event_type_name;

CREATE OR REPLACE VIEW vw_event_funding_summary AS
SELECT e.id AS event_id,
       e.event_code,
       e.title,
       COALESCE(ef.cash_advance_amount, 0) AS cash_advance_amount,
       COALESCE(ef.reimbursement_amount, 0) AS reimbursement_amount,
       COALESCE(ef.refund_amount, 0) AS refund_amount,
       COALESCE(SUM(ee.line_total), 0) AS actual_expenses,
       (COALESCE(ef.cash_advance_amount, 0) + COALESCE(ef.reimbursement_amount, 0) - COALESCE(SUM(ee.line_total), 0) - COALESCE(ef.refund_amount, 0)) AS running_balance
FROM events e
LEFT JOIN event_funding ef ON ef.event_id = e.id
LEFT JOIN event_expenses ee ON ee.event_id = e.id
GROUP BY e.id, e.event_code, e.title, ef.cash_advance_amount, ef.reimbursement_amount, ef.refund_amount;


CREATE OR REPLACE VIEW vw_attendance_session_summary AS
SELECT 
    ar.event_id,
    e.event_code,
    e.title,
    ar.event_date,
    ar.session_code,
    COUNT(*) AS present_count
FROM attendance_records ar
JOIN events e ON e.id = ar.event_id
WHERE ar.attendance_status = 'present'
GROUP BY ar.event_id, e.event_code, e.title, ar.event_date, ar.session_code;


-- =========================================
-- PHASE 7 MASTER DATA DROPDOWNS
-- =========================================
DROP TABLE IF EXISTS dropdown_options;
CREATE TABLE dropdown_options (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_key VARCHAR(50) NOT NULL,
    option_value VARCHAR(150) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dropdown_group_value (group_key, option_value)
) ENGINE=InnoDB;

INSERT INTO dropdown_options (group_key, option_value, sort_order, status) VALUES
('department_office', 'College of Education', 10, 'active'),
('department_office', 'College of Engineering', 20, 'active'),
('department_office', 'Food Technology Department', 30, 'active'),
('department_office', 'Accounting Office', 40, 'active'),
('department_office', 'Registrar Office', 50, 'active'),
('venue', 'University Function Hall', 10, 'active'),
('venue', 'Food Technology Laboratory', 20, 'active'),
('venue', 'Conference Room', 30, 'active'),
('item_category', 'Ingredient', 10, 'active'),
('item_category', 'Meat', 20, 'active'),
('item_category', 'Vegetable', 30, 'active'),
('item_category', 'Packaging', 40, 'active'),
('item_category', 'Utensil / Supply', 50, 'active'),
('menu_category', 'Main Dish', 10, 'active'),
('menu_category', 'Snack', 20, 'active'),
('menu_category', 'Dessert', 30, 'active'),
('menu_category', 'Beverage', 40, 'active'),
('unit', 'kg', 10, 'active'),
('unit', 'g', 20, 'active'),
('unit', 'liter', 30, 'active'),
('unit', 'ml', 40, 'active'),
('unit', 'pack', 50, 'active'),
('unit', 'pc', 60, 'active'),
('unit', 'tray', 70, 'active'),
('vendor', 'ABC Public Market Supplier', 10, 'active'),
('vendor', 'Fresh Harvest Trading', 20, 'active'),
('vendor', 'Campus Grocery Partner', 30, 'active');