-- FILE: fix_database_tables.sql
-- PURPOSE: Fix table name mismatches and missing references

-- Fix 1: Create a view called 'vendors' that points to 'vendors_main'
-- This allows existing code that references 'vendors' table to work
DROP VIEW IF EXISTS vendors;
CREATE VIEW vendors AS 
SELECT 
    vendor_id as id,
    vendor_id,
    vendor_code,
    company_name,
    contact_person,
    email,
    phone,
    mobile,
    website,
    address,
    city,
    state,
    country,
    postal_code,
    category_id,
    tax_number,
    registration_number,
    bank_name,
    bank_account,
    payment_terms,
    credit_limit,
    rating,
    status,
    notes,
    created_by,
    created_at,
    updated_at,
    vendor_name,
    vendor_category,
    is_preferred
FROM vendors_main;

-- Fix 2: Update foreign key constraints that reference non-existent 'vendors' table
-- First, drop the problematic constraint
ALTER TABLE vendor_ratings DROP FOREIGN KEY IF EXISTS vendor_ratings_ibfk_1;

-- Recreate it with correct table reference
ALTER TABLE vendor_ratings 
ADD CONSTRAINT vendor_ratings_ibfk_1 
FOREIGN KEY (vendor_id) REFERENCES vendors_main(vendor_id);

-- Fix 3: Ensure all required workflow rules exist
INSERT IGNORE INTO workflow_rules (
    rule_name, description, min_amount, max_amount, 
    approval_steps, escalation_hours, is_active, 
    effective_from, created_at
) VALUES 
('Micro Purchases', 'Small office supplies and minor expenses', 0.00, 10000.00, 
 '["department_head"]', 8, 1, CURDATE(), NOW()),
 
('Small Purchases', 'Standard departmental purchases', 10001.00, 50000.00, 
 '["department_head"]', 24, 1, CURDATE(), NOW()),
 
('Medium Purchases', 'Significant purchases requiring financial review', 50001.00, 200000.00, 
 '["department_head", "finance"]', 24, 1, CURDATE(), NOW()),
 
('Large Purchases', 'Major purchases requiring procurement review', 200001.00, 500000.00, 
 '["department_head", "finance", "procurement"]', 24, 1, CURDATE(), NOW()),
 
('Major Purchases', 'High-value purchases requiring executive approval', 500001.00, 2000000.00, 
 '["department_head", "finance", "procurement", "md"]', 48, 1, CURDATE(), NOW()),
 
('Capital Expenditure', 'Major capital investments', 2000001.00, NULL, 
 '["department_head", "finance", "procurement", "md"]', 72, 1, CURDATE(), NOW());

-- Fix 4: Ensure required user roles exist
-- Update any existing users with missing role assignments
UPDATE users SET role = 'admin' WHERE id = 2 AND (role IS NULL OR role = '');
UPDATE users SET role = 'admin' WHERE id = 3 AND (role IS NULL OR role = '');
UPDATE users SET role = 'admin' WHERE id = 4 AND (role IS NULL OR role = '');

-- Add sample users for testing if they don't exist
INSERT IGNORE INTO users (
    id, username, email, password_hash, first_name, last_name, 
    role_id, is_active, role, full_name, status, created_at, updated_at
) VALUES 
(100, 'dept.head', 'dept.head@company.com', '$2y$10$szlmEjD1/R.ShuM5JqjzBOUttCgOuTOjJlEn4300ot0HjZkkj9LFC', 
 'Department', 'Head', 6, 1, 'hod', 'Department Head', 'active', NOW(), NOW()),
 
(101, 'finance.mgr', 'finance.mgr@company.com', '$2y$10$szlmEjD1/R.ShuM5JqjzBOUttCgOuTOjJlEn4300ot0HjZkkj9LFC', 
 'Finance', 'Manager', 5, 1, 'finance', 'Finance Manager', 'active', NOW(), NOW()),
 
(102, 'proc.officer', 'proc.officer@company.com', '$2y$10$szlmEjD1/R.ShuM5JqjzBOUttCgOuTOjJlEn4300ot0HjZkkj9LFC', 
 'Procurement', 'Officer', 4, 1, 'procurement', 'Procurement Officer', 'active', NOW(), NOW()),
 
(103, 'managing.director', 'md@company.com', '$2y$10$szlmEjD1/R.ShuM5JqjzBOUttCgOuTOjJlEn4300ot0HjZkkj9LFC', 
 'Managing', 'Director', 2, 1, 'md', 'Managing Director', 'active', NOW(), NOW());

-- Fix 5: Ensure procurement categories are active
UPDATE procurement_categories SET status = 'active' WHERE status != 'active';

-- Fix 6: Clean up any orphaned records or test data
DELETE FROM procurement_requests WHERE request_number LIKE 'TEST-%';
DELETE FROM procurement_request_items WHERE request_id NOT IN (SELECT request_id FROM procurement_requests);
DELETE FROM approval_queue WHERE request_id NOT IN (SELECT request_id FROM procurement_requests);

-- Fix 7: Reset any auto-increment values if needed
-- ALTER TABLE procurement_requests AUTO_INCREMENT = 1;
-- ALTER TABLE vendors_main AUTO_INCREMENT = 1;

-- Verification queries to run after fixes
-- SELECT 'vendors view created' as status, COUNT(*) as count FROM vendors;
-- SELECT 'workflow rules' as status, COUNT(*) as count FROM workflow_rules WHERE is_active = 1;
-- SELECT 'user roles' as status, role, COUNT(*) as count FROM users WHERE is_active = 1 GROUP BY role;
-- SELECT 'procurement categories' as status, COUNT(*) as count FROM procurement_categories WHERE status = 'active';

-- Show final status
SELECT 'Database fixes completed successfully' AS status;