-- ============================================
-- COMPLETE DATABASE SETUP FOR COW FUNDING SYSTEM
-- ============================================

-- Drop database if exists (Optional - Comment this line if you want to keep existing data)
-- DROP DATABASE IF EXISTS cow_funding;

-- Create and use database
CREATE DATABASE IF NOT EXISTS cow_funding;
USE cow_funding;

-- ============================================
-- 1. CREATE TABLES (In correct order with proper foreign keys)
-- ============================================

-- Users table (Parent table)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    mobile VARCHAR(15),
    address TEXT,
    role ENUM('user', 'admin') DEFAULT 'user',
    kyc_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    kyc_doc VARCHAR(255),
    membership_active BOOLEAN DEFAULT TRUE,
    total_donation DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Donations / Payment proof table (Child of users)
CREATE TABLE IF NOT EXISTS donations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2) NOT NULL,
    payment_proof VARCHAR(255),
    status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
);

-- Fund distribution records
CREATE TABLE IF NOT EXISTS fund_distributions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description TEXT,
    amount DECIMAL(10,2) NOT NULL,
    distributed_to VARCHAR(255),
    date DATE,
    INDEX idx_date (date)
);

-- Balance additions table for manual balance additions
CREATE TABLE IF NOT EXISTS balance_additions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2) NOT NULL,
    source VARCHAR(100) NOT NULL,
    description TEXT,
    added_by INT,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_added_at (added_at)
);

-- Create table for organization signatures
CREATE TABLE IF NOT EXISTS org_signatures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    position VARCHAR(50) NOT NULL,
    signature_image VARCHAR(255),
    signatory_name VARCHAR(100),
    designation VARCHAR(100),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_position (position)
);

-- Insert default signatures (Using INSERT IGNORE to avoid duplicates)
INSERT IGNORE INTO org_signatures (position, signatory_name, designation) VALUES 
('president', 'Shri Ram Sharma', 'President'),
('secretary', 'Shri Sita Ram', 'Secretary'),
('treasurer', 'Shri Radhe Shyam', 'Treasurer');

-- Withdrawal requests (Child of users)
CREATE TABLE IF NOT EXISTS withdrawal_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    requested_by INT,
    amount DECIMAL(10,2) NOT NULL,
    reason TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status)
);

-- Membership cancellation requests (Child of users)
CREATE TABLE IF NOT EXISTS cancellation_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    reason TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status)
);

-- ============================================
-- 2. INSERT SAMPLE DATA (Using IGNORE to avoid duplicates)
-- ============================================

-- Insert Admin User (password: admin123)
INSERT IGNORE INTO users (id, name, email, password, role, kyc_status, membership_active, total_donation) VALUES 
(1, 'Admin User', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'approved', 1, 0.00);

-- Insert Sample Users
INSERT IGNORE INTO users (id, name, email, password, mobile, address, kyc_status, membership_active, total_donation) VALUES 
(2, 'Test User', 'user@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '9876543210', 'New Delhi, India', 'approved', 1, 5000.00),
(3, 'Rahul Sharma', 'rahul@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '9876543211', 'Mumbai, India', 'approved', 1, 10000.00),
(4, 'Priya Patel', 'priya@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '9876543212', 'Ahmedabad, India', 'approved', 1, 7500.00),
(5, 'Amit Kumar', 'amit@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '9876543213', 'Bangalore, India', 'pending', 1, 0.00),
(6, 'Sunita Reddy', 'sunita@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '9876543214', 'Hyderabad, India', 'approved', 0, 3000.00);

-- ============================================
-- 3. INSERT DONATIONS
-- ============================================

INSERT IGNORE INTO donations (id, user_id, amount, payment_proof, status, uploaded_at) VALUES 
(1, 2, 5000.00, 'uploads/payment_proof/payment1.jpg', 'verified', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(2, 3, 10000.00, 'uploads/payment_proof/payment2.jpg', 'verified', DATE_SUB(NOW(), INTERVAL 3 DAY)),
(3, 2, 2000.00, 'uploads/payment_proof/payment3.jpg', 'verified', DATE_SUB(NOW(), INTERVAL 1 DAY)),
(4, 4, 7500.00, 'uploads/payment_proof/payment4.jpg', 'verified', DATE_SUB(NOW(), INTERVAL 7 DAY)),
(5, 3, 5000.00, 'uploads/payment_proof/payment5.jpg', 'verified', DATE_SUB(NOW(), INTERVAL 2 DAY)),
(6, 2, 3000.00, 'uploads/payment_proof/payment6.jpg', 'verified', DATE_SUB(NOW(), INTERVAL 4 DAY));

-- ============================================
-- 4. INSERT FUND DISTRIBUTIONS
-- ============================================

INSERT IGNORE INTO fund_distributions (id, description, amount, distributed_to, date) VALUES 
(1, 'Cow Feed Purchase for Winter Season', 25000.00, 'Shri Gaushala, Vrindavan', CURDATE()),
(2, 'Medical Camp for Cows - Vaccination Drive', 15000.00, 'Vet Hospital, Mathura', CURDATE()),
(3, 'Shelter Maintenance & Repair', 30000.00, 'Construction Team, Gokul Dham', CURDATE()),
(4, 'Cow Protection Awareness Program', 10000.00, 'NGO Partners', DATE_SUB(CURDATE(), INTERVAL 10 DAY)),
(5, 'Emergency Medical Treatment', 8000.00, 'Cow Hospital, Delhi', DATE_SUB(CURDATE(), INTERVAL 15 DAY));

-- ============================================
-- 5. INSERT BALANCE ADDITIONS (Manual/Offline)
-- ============================================

INSERT IGNORE INTO balance_additions (id, amount, source, description, added_by, added_at) VALUES 
(1, 15000.00, 'Offline Donation', 'Cash donation from temple event - Devotee contributions', 1, DATE_SUB(NOW(), INTERVAL 8 DAY)),
(2, 5000.00, 'Bank Transfer', 'NEFT transfer from corporate sponsor - ABC Corp', 1, DATE_SUB(NOW(), INTERVAL 4 DAY)),
(3, 25000.00, 'Cheque', 'Cheque donation from Charity Foundation', 1, DATE_SUB(NOW(), INTERVAL 2 DAY));

-- ============================================
-- 6. INSERT WITHDRAWAL REQUESTS
-- ============================================

INSERT IGNORE INTO withdrawal_requests (id, requested_by, amount, reason, status, requested_at) VALUES 
(1, 2, 1000.00, 'Emergency personal need - Medical expenses', 'pending', NOW()),
(2, 4, 2000.00, 'Educational fees for children', 'pending', DATE_SUB(NOW(), INTERVAL 3 DAY));

-- ============================================
-- 7. INSERT CANCELLATION REQUESTS
-- ============================================

INSERT IGNORE INTO cancellation_requests (id, user_id, reason, status, requested_at) VALUES 
(1, 2, 'Financial constraints - Unable to continue membership', 'pending', NOW()),
(2, 6, 'Moving to another city', 'pending', DATE_SUB(NOW(), INTERVAL 5 DAY));

-- ============================================
-- 8. UPDATE USER TOTAL DONATIONS (Based on verified donations)
-- ============================================

UPDATE users u 
SET u.total_donation = (
    SELECT COALESCE(SUM(d.amount), 0) 
    FROM donations d 
    WHERE d.user_id = u.id AND d.status = 'verified'
)
WHERE u.role = 'user';

-- ============================================
-- 9. CREATE VIEWS
-- ============================================

-- Drop existing views if they exist
DROP VIEW IF EXISTS vw_total_funds;
DROP VIEW IF EXISTS vw_member_stats;
DROP VIEW IF EXISTS vw_donation_summary;
DROP VIEW IF EXISTS vw_fund_summary;

-- View: Total Funds (Donations + Balance Additions)
CREATE VIEW vw_total_funds AS
SELECT 
    (SELECT COALESCE(SUM(amount), 0) FROM donations WHERE status = 'verified') as total_donations,
    (SELECT COALESCE(SUM(amount), 0) FROM balance_additions) as total_additions,
    (SELECT COALESCE(SUM(amount), 0) FROM donations WHERE status = 'verified') + 
    (SELECT COALESCE(SUM(amount), 0) FROM balance_additions) as total_funds_received,
    (SELECT COALESCE(SUM(amount), 0) FROM fund_distributions) as total_distributed,
    ((SELECT COALESCE(SUM(amount), 0) FROM donations WHERE status = 'verified') + 
     (SELECT COALESCE(SUM(amount), 0) FROM balance_additions) - 
     (SELECT COALESCE(SUM(amount), 0) FROM fund_distributions)) as available_balance;

-- View: Member Statistics
CREATE VIEW vw_member_stats AS
SELECT 
    COUNT(*) as total_members,
    SUM(CASE WHEN kyc_status = 'approved' THEN 1 ELSE 0 END) as approved_kyc,
    SUM(CASE WHEN kyc_status = 'pending' THEN 1 ELSE 0 END) as pending_kyc,
    SUM(CASE WHEN kyc_status = 'rejected' THEN 1 ELSE 0 END) as rejected_kyc,
    SUM(CASE WHEN membership_active = 1 THEN 1 ELSE 0 END) as active_memberships,
    SUM(CASE WHEN membership_active = 0 THEN 1 ELSE 0 END) as inactive_memberships
FROM users 
WHERE role = 'user';

-- View: Donation Summary by User
CREATE VIEW vw_donation_summary AS
SELECT 
    u.id as user_id,
    u.name,
    u.email,
    u.mobile,
    u.kyc_status,
    u.membership_active,
    COUNT(d.id) as total_donations_count,
    COALESCE(SUM(d.amount), 0) as total_donation_amount,
    MAX(d.uploaded_at) as last_donation_date,
    u.created_at as member_since
FROM users u
LEFT JOIN donations d ON u.id = d.user_id AND d.status = 'verified'
WHERE u.role = 'user'
GROUP BY u.id, u.name, u.email, u.mobile, u.kyc_status, u.membership_active, u.created_at
ORDER BY total_donation_amount DESC;

-- View: Fund Summary with Monthly Breakdown
CREATE VIEW vw_fund_summary AS
SELECT 
    DATE_FORMAT(date, '%Y-%m') as month,
    COUNT(*) as distribution_count,
    SUM(amount) as total_distributed
FROM fund_distributions
GROUP BY DATE_FORMAT(date, '%Y-%m')
ORDER BY month DESC;

-- ============================================
-- 10. CREATE STORED PROCEDURES (Optional)
-- ============================================

DELIMITER //

-- Drop procedure if exists
DROP PROCEDURE IF EXISTS sp_dashboard_stats//

-- Procedure to get dashboard statistics
CREATE PROCEDURE sp_dashboard_stats()
BEGIN
    -- Total funds
    SELECT * FROM vw_total_funds;
    
    -- Member stats
    SELECT * FROM vw_member_stats;
    
    -- Recent donations
    SELECT d.id, u.name, d.amount, d.uploaded_at, d.status
    FROM donations d
    JOIN users u ON d.user_id = u.id
    WHERE d.status = 'verified'
    ORDER BY d.uploaded_at DESC
    LIMIT 10;
    
    -- Recent distributions
    SELECT * FROM fund_distributions
    ORDER BY date DESC
    LIMIT 10;
END//

DELIMITER ;

-- ============================================
-- 11. CREATE INDEXES FOR BETTER PERFORMANCE
-- ============================================

-- Drop indexes if they exist and recreate
DROP INDEX IF EXISTS idx_donations_user_status ON donations;
DROP INDEX IF EXISTS idx_donations_uploaded ON donations;
DROP INDEX IF EXISTS idx_users_role ON users;
DROP INDEX IF EXISTS idx_users_kyc ON users;
DROP INDEX IF EXISTS idx_balance_added_by ON balance_additions;

-- Create indexes
CREATE INDEX idx_donations_user_status ON donations(user_id, status);
CREATE INDEX idx_donations_uploaded ON donations(uploaded_at);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_kyc ON users(kyc_status);
CREATE INDEX idx_balance_added_by ON balance_additions(added_by);

-- ============================================
-- 12. DISPLAY SUCCESS MESSAGE AND SUMMARY
-- ============================================

SELECT '=========================================' as '';
SELECT '✅ DATABASE SETUP COMPLETED SUCCESSFULLY!' as STATUS;
SELECT '=========================================' as '';

-- Display summary counts
SELECT 
    '📊 DATABASE SUMMARY' as 'Category',
    (SELECT COUNT(*) FROM users) as 'Total Users',
    (SELECT COUNT(*) FROM donations) as 'Total Donations',
    (SELECT COUNT(*) FROM fund_distributions) as 'Distributions',
    (SELECT COUNT(*) FROM balance_additions) as 'Balance Additions',
    (SELECT COUNT(*) FROM withdrawal_requests) as 'Withdrawal Requests',
    (SELECT COUNT(*) FROM cancellation_requests) as 'Cancellation Requests';

-- Display fund summary
SELECT '💰 FUND SUMMARY' as 'Category';
SELECT * FROM vw_total_funds;

-- Display member summary
SELECT '👥 MEMBER SUMMARY' as 'Category';
SELECT * FROM vw_member_stats;

-- ============================================
-- END OF SCRIPT
-- ============================================