-- ============================================
-- Customer Care Complaint Portal - Database Schema
-- Import this file into your phpMyAdmin
-- ============================================

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

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'supervisor', 'agent') NOT NULL DEFAULT 'agent',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Complaint categories
CREATE TABLE complaint_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Complaints table
CREATE TABLE complaints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id VARCHAR(20) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL,
    customer_email VARCHAR(150),
    customer_phone VARCHAR(30),
    category_id INT,
    subject VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    priority ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    status ENUM('new', 'assigned', 'in_progress', 'escalated', 'resolved', 'closed') NOT NULL DEFAULT 'new',
    assigned_to INT,
    created_by INT NOT NULL,
    resolved_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES complaint_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Complaint activity/audit log
CREATE TABLE complaint_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    complaint_id INT NOT NULL,
    user_id INT NOT NULL,
    action ENUM('created', 'status_changed', 'priority_changed', 'assigned', 'note_added', 'escalated', 'resolved', 'closed') NOT NULL,
    old_value VARCHAR(255),
    new_value VARCHAR(255),
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (complaint_id) REFERENCES complaints(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================
-- Default data
-- ============================================

-- Default admin user (password: Admin@123)
INSERT INTO users (name, email, password, role) VALUES
('Admin', 'admin@yourcompany.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');

-- Default complaint categories
INSERT INTO complaint_categories (name, description) VALUES
('Transaction Failed', 'Payment or transfer transaction failures'),
('Account Locked', 'User account locked or restricted'),
('KYC Issue', 'Know Your Customer verification problems'),
('Unauthorized Transaction', 'Suspected fraud or unauthorized activity'),
('App Bug', 'Technical issues with the application'),
('Delayed Transfer', 'Transfers taking longer than expected'),
('Card Issue', 'Debit/credit card related problems'),
('Other', 'General complaints');