-- Database Schema for IPS Software
-- Designed based on provided documents: Case Scenarios, Sub-Module Features, and Menu Structure.
-- Assumes MySQL 8.0+ with InnoDB engine for referential integrity.
-- Key entities: Users, Roles, Permissions, Invoices, Follow-ups, Calls, etc.
-- Currency is NGN (₦), stored as DECIMAL.
-- Timestamps use DATETIME with timezone support if needed (assuming UTC).
-- Enums used for fixed values where appropriate.

-- 1. Roles Table
-- Pre-configured roles: Admin, Supervisor, IPO. Supports custom roles.
CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,  -- e.g., 'Admin', 'Supervisor', 'IPO', 'CustomRole'
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Permissions Table
-- Granular permissions for RBAC, e.g., 'view_dashboard', 'manage_users'.
CREATE TABLE permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,  -- e.g., 'create_user', 'view_invoices', 'escalate_followup'
    description TEXT,
    module VARCHAR(50),  -- e.g., 'User Management', 'Invoice Management'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3. Role-Permissions Pivot Table
-- Many-to-many relationship for RBAC.
CREATE TABLE role_permissions (
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- 4. Users Table
-- Includes IPOs, Supervisors, Admins. NIN is National ID Number.
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    nin VARCHAR(20) UNIQUE,  -- National Identification Number
    role_id INT UNSIGNED NOT NULL,
    supervisor_id INT UNSIGNED NULL,  -- For IPOs, references another user (Supervisor)
    profile_photo VARCHAR(255) NULL,  -- Path to uploaded photo
    password_hash VARCHAR(255) NOT NULL,  -- Hashed password
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    last_login DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (supervisor_id) REFERENCES users(id)
);

-- Index for faster lookups
-- CREATE INDEX idx_users_email ON users(email);
-- CREATE INDEX idx_users_role ON users(role_id);

-- 5. Taxpayers Table
-- Separate from invoices for normalization, as taxpayers may have multiple invoices.
CREATE TABLE taxpayers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    type ENUM('individual', 'corporate') NOT NULL,
    nin_or_cac VARCHAR(50) NULL,  -- NIN for individual, CAC for corporate
    address TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 6. Invoices Table
-- Synced from IBS, overdue invoices (10+ days).
-- Updated table structure
CREATE TABLE invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,  -- e.g., 'KD2025-1001'
    invoice_type VARCHAR(50) NOT NULL,  -- e.g., 'direct', 'tcc', 'presumptive'
    amount DECIMAL(15, 2) NOT NULL,  -- e.g., 50000.00
    description TEXT NOT NULL,  -- e.g., 'property tax'
    due_date DATE NOT NULL,
    overdue_days INT NOT NULL,
    status ENUM('open', 'active', 'paid', 'closed') DEFAULT 'open',
    taxpayer_id INT UNSIGNED NOT NULL,
    tax_number VARCHAR(50) NOT NULL,  -- Tax identification number
    mda VARCHAR(100) NULL,  -- Ministry/Department/Agency
    assigned_to INT UNSIGNED NULL,  -- IPO user_id
    paid_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    synced_from_ibs_at DATETIME NULL,  -- Last sync time
    FOREIGN KEY (taxpayer_id) REFERENCES taxpayers(id),
    FOREIGN KEY (assigned_to) REFERENCES users(id)
);

-- Indexes
-- CREATE INDEX idx_invoices_status ON invoices(status);
-- CREATE INDEX idx_invoices_assigned_to ON invoices(assigned_to);

-- 7. FollowUps Table
-- Tracks follow-up processes for invoices.
CREATE TABLE follow_ups (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    ipo_id INT UNSIGNED NOT NULL,
    status ENUM('active', 'rescheduled', 'escalated', 'closed') DEFAULT 'active',
    attempts INT DEFAULT 0,
    last_contact DATETIME NULL,
    next_action DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (ipo_id) REFERENCES users(id)
);

-- 8. Calls Table
-- Call attempts and details.
CREATE TABLE calls (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    follow_up_id INT UNSIGNED NOT NULL,
    start_time DATETIME NOT NULL,
    duration INT NULL,  -- In seconds
    status ENUM('initiating', 'connected', 'failed', 'unreachable', 'invalid_number') DEFAULT 'initiating',
    failure_reason VARCHAR(255) NULL,
    recording_file VARCHAR(255) NULL,  -- Path to MP3/OGG file
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (follow_up_id) REFERENCES follow_ups(id) ON DELETE CASCADE
);

-- 9. Conversation Notes Table
-- Notes from calls/follow-ups.
CREATE TABLE conversation_notes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    call_id INT UNSIGNED NULL,
    follow_up_id INT UNSIGNED NOT NULL,
    is_taxpayer_generator BOOLEAN DEFAULT FALSE,
    reason_not_paying_id INT UNSIGNED NULL,  -- References config table
    need_support BOOLEAN DEFAULT FALSE,
    note_text TEXT NOT NULL,  -- Limited to 200 chars in app, but TEXT for flexibility
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL,
    FOREIGN KEY (follow_up_id) REFERENCES follow_ups(id) ON DELETE CASCADE
    -- reason_not_paying_id foreign key defined later
);

-- 10. Escalations Table
-- Escalated issues.
CREATE TABLE escalations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    follow_up_id INT UNSIGNED NOT NULL,
    reason TEXT NOT NULL,  -- 100+ chars
    escalated_by INT UNSIGNED NOT NULL,  -- IPO user_id
    assigned_to INT UNSIGNED NOT NULL,  -- Supervisor user_id
    status ENUM('pending', 'in_review', 'resolved', 'rejected') DEFAULT 'pending',
    resolution TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (follow_up_id) REFERENCES follow_ups(id) ON DELETE CASCADE,
    FOREIGN KEY (escalated_by) REFERENCES users(id),
    FOREIGN KEY (assigned_to) REFERENCES users(id)
);

-- 11. Closures Table
-- Invoice closures.
CREATE TABLE closures (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    reason TEXT NOT NULL,
    closed_by INT UNSIGNED NOT NULL,  -- IPO or Supervisor
    approved_by INT UNSIGNED NULL,  -- Supervisor approval
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (closed_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)
);

-- 12. Appointments Table
-- Scheduled follow-ups.
CREATE TABLE appointments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    follow_up_id INT UNSIGNED NOT NULL,
    scheduled_time DATETIME NOT NULL,
    status ENUM('scheduled', 'completed', 'missed', 'cancelled') DEFAULT 'scheduled',
    reminder_sent BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (follow_up_id) REFERENCES follow_ups(id) ON DELETE CASCADE
);

-- 13. Performance Targets Table
-- Monthly/period-based targets for IPOs.
CREATE TABLE performance_targets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    period DATE NOT NULL,  -- e.g., '2025-09-01' for September
    volume_target INT NOT NULL,  -- Number of invoices
    amount_target DECIMAL(15, 2) NOT NULL,
    achieved_volume INT DEFAULT 0,
    achieved_amount DECIMAL(15, 2) DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY (user_id, period),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 14. Audit Logs Table
-- For compliance and activity tracking.
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,  -- e.g., 'login', 'escalate', 'close_invoice'
    entity_type VARCHAR(50) NULL,  -- e.g., 'invoice', 'user'
    entity_id BIGINT NULL,
    details JSON NULL,  -- Flexible details storage
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- 15. Security Logs Table
-- Login attempts, etc.
CREATE TABLE security_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    event_type ENUM('login_success', 'login_failure', 'password_reset', 'suspicious_activity') NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    device_info VARCHAR(255) NULL,
    details TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- 16. Reasons for Non-Payment Config Table
-- Configurable list (19 predefined).
CREATE TABLE reasons_not_paying (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reason_text VARCHAR(255) NOT NULL UNIQUE,  -- e.g., 'Missed payment deadline', 'Cannot afford to pay'
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Add foreign key to conversation_notes
ALTER TABLE conversation_notes ADD FOREIGN KEY (reason_not_paying_id) REFERENCES reasons_not_paying(id);

-- 17. Notification Templates Table
-- For SMS/Email templates.
CREATE TABLE notification_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    type ENUM('email', 'sms') NOT NULL,
    content TEXT NOT NULL,  -- With placeholders, e.g., {invoice_number}
    language ENUM('english', 'hausa') DEFAULT 'english',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 18. Notifications Table
-- Sent notifications log.
CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type ENUM('sms', 'email', 'in_app', 'push') NOT NULL,
    recipient VARCHAR(255) NOT NULL,  -- Phone or email
    content TEXT NOT NULL,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    sent_at DATETIME NULL,
    failure_reason TEXT NULL,
    related_entity_type VARCHAR(50) NULL,  -- e.g., 'invoice'
    related_entity_id BIGINT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 19. System Settings Table
-- Key-value for configurations like sync intervals, retention policies.
CREATE TABLE system_settings (
    key_name VARCHAR(100) PRIMARY KEY,
    value TEXT NOT NULL,
    description TEXT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 20. Integration Logs Table
-- For IBS/Telephony integrations, error handling.
CREATE TABLE integration_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_type ENUM('ibs', 'telephony') NOT NULL,
    event_type ENUM('sync', 'call', 'error') NOT NULL,
    details JSON NULL,
    status ENUM('success', 'failure') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Initial Data (Optional, for setup)
-- Insert default roles
INSERT INTO roles (name, description) VALUES
('Admin', 'Full system access'),
('Supervisor', 'Team management and oversight'),
('IPO', 'Invoice follow-up officer');

-- Insert sample permissions (expand as needed)
INSERT INTO permissions (name, description, module) VALUES
('view_dashboard', 'View executive dashboard', 'Dashboard'),
('manage_users', 'Create/edit users', 'User Management'),
('view_invoices', 'View open invoices', 'Invoice Management');

-- Assign permissions to roles (example)
INSERT INTO role_permissions (role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3),  -- Admin has all
(2, 1), (2, 3),  -- Supervisor has dashboard and invoices
(3, 1), (3, 3);  -- IPO has dashboard and invoices

-- Insert default reasons
INSERT INTO reasons_not_paying (reason_text) VALUES
('Missed payment deadline'),
('Cannot afford to pay'),
('Forgot about it');

-- Default settings
INSERT INTO system_settings (key_name, value) VALUES
('ibs_sync_interval_minutes', '15'),
('invoice_archive_days', '20'),
('call_recording_retention_months', '6');