-- CEMS Nepal Test Data Seed Script (MySQL)
-- Run this in your MySQL client (e.g., phpMyAdmin, MySQL Workbench)

USE cems_db;

-- Clear existing data (Optional - use with caution)
-- SET FOREIGN_KEY_CHECKS = 0;
-- TRUNCATE TABLE transaction_tb;
-- TRUNCATE TABLE project_tb;
-- TRUNCATE TABLE employee_tb;
-- SET FOREIGN_KEY_CHECKS = 1;

-- 1. Seed Projects
INSERT INTO project_tb (name, code, location, status, budget) VALUES 
('Kathmandu View Tower', 'KVT-01', 'Old Bus Park, Kathmandu', 'ongoing', 500000000.00),
('Pokhara International Airport Road', 'PAR-02', 'Chinnechaur, Pokhara', 'ongoing', 120000000.00),
('Bhairahawa Special Economic Zone', 'BSEZ-03', 'Bhairahawa, Lumbini', 'completed', 85000000.00),
('Dharan-Chatara-Gaighat Road', 'DCG-04', 'East-West Highway Connection', 'pending', 250000000.00),
('Lumbini Master Plan Phase II', 'LMP-05', 'Lumbini, Rupandehi', 'ongoing', 300000000.00)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- 2. Seed Employees
INSERT INTO employee_tb (salutation, full_name, email, phone, designation, is_active) VALUES 
('Mr.', 'Aashish Bdr Chettri', 'aashish@cems.com', '9841234567', 'Senior Engineer', 1),
('Mr.', 'Ram Kumar Shrestha', 'ram@cems.com', '9801234567', 'Site Supervisor', 1),
('Ms.', 'Sita Maya', 'sita@cems.com', '9811234567', 'Accountant', 1),
('Mr.', 'Hari Prasad Luitel', 'hari@cems.com', '9851234567', 'Project Manager', 1),
('Mrs.', 'Maya Devi', 'maya@cems.com', '9861234567', 'Safety Officer', 1)
ON DUPLICATE KEY UPDATE full_name=VALUES(full_name);

-- 3. Seed Transactions
INSERT INTO transaction_tb (title, type, category, amount, transaction_date, reference_number, is_active) VALUES 
('Initial Project Deposit - KVT', 'income', 'Revenue', 5000000.00, '2024-05-01', 'INV-1001', 1),
('Cement Purchase - Ultratech', 'expense', 'Material', 120000.00, '2024-05-05', 'VOU-202', 1),
('Worker Wages - Week 1', 'expense', 'Labor', 85000.00, '2024-05-07', 'WAG-01', 1),
('Equipment Lease Payment', 'expense', 'Asset', 45000.00, '2024-05-10', 'LES-99', 1),
('Consultancy Fee Received', 'income', 'Service', 200000.00, '2024-05-12', 'INV-1002', 1)
ON DUPLICATE KEY UPDATE title=VALUES(title);

-- 4. Seed Notifications (for test purpose)
-- Assuming a table named notification_tb exists based on my API addition
-- If it doesn't exist, this will fail safely.
-- INSERT INTO notification_tb (title, message, is_read, created_at) VALUES 
-- ('New Project Assigned', 'You have been assigned to Kathmandu View Tower project.', 0, NOW()),
-- ('Low Stock Alert', 'Cement stock is below 10% in Pokhara site.', 0, NOW()),
-- ('Budget Overrun', 'Labor costs in Lumbini project exceeded monthly budget.', 0, NOW());