-- FeatureRequest (Internal Feature Intake & Evaluation Portal) Database Schema
-- MySQL 8.0 / MariaDB Compatible

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


-- 2. Categories Table
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    color_code VARCHAR(20) DEFAULT '#1fbbb8',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Applications Table (Target System/App Identifier)
CREATE TABLE applications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    color_code VARCHAR(20) DEFAULT '#1fbbb8',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. FR ID Sequence Table (for IFR-YY### sequence tracking)
CREATE TABLE fr_id_sequence (
    year_prefix CHAR(2) NOT NULL PRIMARY KEY,
    current_seq INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Feature Requests Table
CREATE TABLE feature_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fr_id VARCHAR(15) NOT NULL UNIQUE, -- Format: IFR-YY001
    submitter_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    application_id INT UNSIGNED NULL,
    description TEXT NOT NULL,
    problem_statement TEXT NOT NULL,
    current_workaround TEXT NULL,
    proposed_solution TEXT NOT NULL,
    target_users VARCHAR(255) NOT NULL,
    success_metrics TEXT NULL,
    status ENUM('Draft', 'Submitted', 'Under Review', 'Approved', 'Rejected', 'Deferred', 'In Development', 'Testing', 'UAT', 'Released', 'Cancelled') NOT NULL DEFAULT 'Draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (submitter_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Business Impact Table (Section 3)
CREATE TABLE fr_business_impact (
    fr_id INT UNSIGNED PRIMARY KEY,
    revenue_impact_type ENUM('Direct Revenue Generation', 'Cost Savings', 'Customer Retention', 'Indirect', 'None') NOT NULL,
    revenue_estimate DECIMAL(12, 2) DEFAULT 0.00,
    customers_affected INT UNSIGNED DEFAULT 0,
    customer_segment ENUM('Enterprise', 'Mid-Market', 'SMB', 'All') NOT NULL DEFAULT 'All',
    urgency_level ENUM('Critical', 'High', 'Medium', 'Low') NOT NULL DEFAULT 'Medium',
    target_release_quarter VARCHAR(10) NOT NULL, -- e.g. Q3 2026
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Technical Assessment Table (Section 4)
CREATE TABLE fr_technical_assessment (
    fr_id INT UNSIGNED PRIMARY KEY,
    complexity ENUM('XS', 'S', 'M', 'L', 'XL') NOT NULL DEFAULT 'M',
    dev_estimate_days INT UNSIGNED DEFAULT 0,
    dependencies TEXT NULL,
    technical_risks TEXT NULL,
    integration_requirements TEXT NULL,
    assessed_by INT UNSIGNED NULL,
    assessed_at TIMESTAMP NULL,
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (assessed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Committee Scores Table (Section 5)
CREATE TABLE committee_scores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fr_id INT UNSIGNED NOT NULL,
    committee_member_id INT UNSIGNED NOT NULL,
    strategic_alignment TINYINT UNSIGNED NOT NULL CHECK (strategic_alignment BETWEEN 1 AND 5), -- 25%
    revenue_impact_score TINYINT UNSIGNED NOT NULL CHECK (revenue_impact_score BETWEEN 1 AND 5), -- 25%
    customer_value TINYINT UNSIGNED NOT NULL CHECK (customer_value BETWEEN 1 AND 5), -- 20%
    technical_feasibility TINYINT UNSIGNED NOT NULL CHECK (technical_feasibility BETWEEN 1 AND 5), -- 20%
    urgency_score TINYINT UNSIGNED NOT NULL CHECK (urgency_score BETWEEN 1 AND 5), -- 10%
    weighted_total DECIMAL(4, 2) NOT NULL,
    comments TEXT NULL,
    scored_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_member_score (fr_id, committee_member_id),
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (committee_member_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. Committee Decisions Table (Section 6)
CREATE TABLE committee_decisions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fr_id INT UNSIGNED NOT NULL UNIQUE,
    status ENUM('Approved', 'Rejected', 'Deferred', 'Needs More Info') NOT NULL,
    rationale TEXT NOT NULL,
    assigned_team VARCHAR(100) NULL,
    target_sprint VARCHAR(50) NULL,
    priority_ranking INT UNSIGNED NULL,
    decided_by INT UNSIGNED NOT NULL,
    decided_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (decided_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. Action Items Table
CREATE TABLE action_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fr_id INT UNSIGNED NOT NULL,
    description VARCHAR(255) NOT NULL,
    owner VARCHAR(100) NOT NULL,
    deadline DATE NULL,
    status ENUM('Pending', 'In Progress', 'Completed') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. Implementation Tracking Table (Section 7)
CREATE TABLE implementation_tracking (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fr_id INT UNSIGNED NOT NULL UNIQUE,
    dev_status ENUM('Backlog', 'In Design', 'In Development', 'Testing', 'UAT', 'Released', 'Cancelled') NOT NULL DEFAULT 'Backlog',
    design_doc_link VARCHAR(500) NULL,
    pr_links TEXT NULL,
    release_notes_link VARCHAR(500) NULL,
    actual_release_date DATE NULL,
    updated_by INT UNSIGNED NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12. Activity Log Table (Audit Trail)
CREATE TABLE activity_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fr_id INT UNSIGNED NULL,
    user_id INT UNSIGNED NOT NULL,
    action VARCHAR(100) NOT NULL,
    details TEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 13. Notifications Table
CREATE TABLE notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    fr_id INT UNSIGNED NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 15. Attachments Table (Max 10MB per file, allowed: PDF, DOCX, XLSX, PNG, JPG)
CREATE TABLE attachments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fr_id INT UNSIGNED NOT NULL,
    filename VARCHAR(255) NOT NULL,
    filepath VARCHAR(500) NOT NULL,
    file_size INT UNSIGNED NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    uploaded_by INT UNSIGNED NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fr_id) REFERENCES feature_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed Categories
INSERT INTO categories (name, description, color_code) VALUES
('UI/UX', 'User interface design, usability improvements, styling', '#1fbbb8'),
('API / Integration', 'REST APIs, webhooks, 3rd party service integrations', '#8b5cf6'),
('Performance', 'Speed optimization, database query tuning, caching', '#10b981'),
('Security', 'Authentication, authorization, vulnerability fixes', '#ff0066'),
('Reporting / Analytics', 'Dashboards, data exports, metric tracking', '#f59e0b'),
('Workflow / Automation', 'Process automation, email alerts, background jobs', '#0eb7fa'),
('Other', 'General enhancements and miscellaneous features', '#64748b');

-- Seed Target Applications
INSERT INTO applications (name, description, color_code) VALUES
('BlastOFF', 'High-speed messaging and broadcast platform', '#1fbbb8'),
('Bual CloudPhone Android', 'Android mobile softphone application', '#10b981'),
('Bual CloudPhone iOS', 'iOS mobile softphone application', '#0eb7fa'),
('Bual Support System', 'Customer ticketing and helpdesk portal', '#f59e0b'),
('Bual Feature Request', 'Internal feature intake and evaluation system', '#8b5cf6'),
('Maya', 'AI assistant and automation engine', '#ff0066'),
('Oversee', 'Network monitoring and infrastructure analytics', '#6366f1'),
('Overtask', 'Task management and workflow assignment tool', '#ec4899'),
('TM UCS Android', 'Unified communications client for Android', '#14b8a6'),
('TM UCS iOS', 'Unified communications client for iOS', '#3b82f6');
