-- Dashboard System Database Tables
-- Copy and paste this entire content into phpMyAdmin SQL tab

-- Create divisions table
CREATE TABLE divisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    code VARCHAR(10) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create application forms table
CREATE TABLE application_forms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    division_id INT NOT NULL,
    form_type ENUM('International Exhibition', 'Regional Offices') NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_by INT NULL,
    reviewed_at TIMESTAMP NULL,
    review_notes TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (division_id) REFERENCES divisions(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Create international exhibition data table
CREATE TABLE international_exhibition_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    serial_no VARCHAR(50) NOT NULL,
    activity_achievement VARCHAR(255) NOT NULL,
    activity_date DATE NOT NULL,
    brief_description TEXT NOT NULL,
    objective TEXT NOT NULL,
    impact_significance_outcome TEXT NOT NULL,
    FOREIGN KEY (application_id) REFERENCES application_forms(id) ON DELETE CASCADE
);

-- Create regional offices data table
CREATE TABLE regional_offices_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    country VARCHAR(100) NOT NULL,
    produce VARCHAR(255) NOT NULL,
    date_wise DATE NULL,
    year_wise YEAR NULL,
    file_path VARCHAR(500),
    file_type ENUM('CSV', 'PDF') NOT NULL,
    FOREIGN KEY (application_id) REFERENCES application_forms(id) ON DELETE CASCADE
);

-- Create work submissions table (for backward compatibility)
CREATE TABLE work_submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    file_path VARCHAR(500),
    submission_type ENUM('document', 'image', 'report', 'other') DEFAULT 'other',
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_by INT NULL,
    reviewed_at TIMESTAMP NULL,
    review_notes TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Create activity logs table
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(255) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert divisions
INSERT INTO divisions (name, code, description) VALUES
('Textile', 'TXT', 'Textile and Fashion Division'),
('Engineering', 'ENG', 'Engineering and Technology Division'),
('Agro', 'AGRO', 'Agriculture and Food Division'),
('Service', 'SVC', 'Service Industry Division'),
('IMDD', 'IMDD', 'International Marketing and Development Division'),
('WE', 'WE', 'Women Empowerment Division');

-- Insert sample application forms
INSERT INTO application_forms (user_id, division_id, form_type, status) VALUES
(4, 1, 'International Exhibition', 'pending'),
(5, 2, 'Regional Offices', 'approved'),
(6, 3, 'International Exhibition', 'pending'),
(7, 4, 'Regional Offices', 'pending'),
(8, 5, 'International Exhibition', 'approved'),
(9, 6, 'Regional Offices', 'pending');

-- Insert sample international exhibition data
INSERT INTO international_exhibition_data (application_id, serial_no, activity_achievement, activity_date, brief_description, objective, impact_significance_outcome) VALUES
(1, 'TXT-001', 'Textile Innovation Showcase', '2024-01-15', 'Showcasing latest textile innovations and sustainable practices', 'To promote sustainable textile practices globally', 'Increased awareness of sustainable textile practices, 50+ international partnerships'),
(3, 'AGRO-001', 'Agricultural Technology Expo', '2024-02-20', 'Demonstrating advanced agricultural technologies', 'To promote modern farming techniques', 'Adoption of new technologies by 200+ farmers, improved crop yields'),
(5, 'IMDD-001', 'International Marketing Summit', '2024-03-10', 'Global marketing strategies and partnerships', 'To expand international market reach', 'Established partnerships in 15+ countries, 30% increase in exports');

-- Insert sample regional offices data
INSERT INTO regional_offices_data (application_id, country, produce, date_wise, year_wise, file_path, file_type) VALUES
(2, 'Germany', 'Engineering Equipment', '2024-01-01', 2024, 'uploads/engineering_germany_2024.pdf', 'PDF'),
(4, 'Japan', 'Service Solutions', '2024-02-01', 2024, 'uploads/service_japan_2024.csv', 'CSV'),
(6, 'USA', 'Women Empowerment Programs', '2024-03-01', 2024, 'uploads/we_usa_2024.pdf', 'PDF');

-- Insert sample work submissions
INSERT INTO work_submissions (user_id, title, description, submission_type, status) VALUES
(4, 'Textile Industry Report', 'Quarterly textile industry analysis', 'report', 'pending'),
(5, 'Engineering Project Documentation', 'Engineering project completion report', 'document', 'approved'),
(6, 'Agricultural Research Paper', 'Latest agricultural research findings', 'document', 'pending');

-- Insert sample activity logs
INSERT INTO activity_logs (user_id, action, description, ip_address) VALUES
(1, 'login', 'Super Admin logged in', '192.168.1.100'),
(2, 'login', 'Admin logged in', '192.168.1.101'),
(3, 'submission_created', 'Created work submission: Q2 Performance Analysis', '192.168.1.102'),
(4, 'submission_created', 'Created work submission: Social Media Content Review', '192.168.1.103'),
(2, 'status_updated', 'Updated submission status to: approved', '192.168.1.101'),
(1, 'status_updated', 'Updated submission status to: rejected', '192.168.1.100');
