-- ============================================================
-- Visa Services Web App — Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS visa_portal;
USE visa_portal;

-- Users Table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  phone VARCHAR(20),
  password VARCHAR(255) NOT NULL,
  role ENUM('customer', 'agent', 'manager', 'admin') DEFAULT 'customer',
  is_active TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Countries Table
CREATE TABLE countries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  flag_emoji VARCHAR(10),
  is_active TINYINT DEFAULT 1
);

-- Visa Types Table
CREATE TABLE visa_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  country_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  requirements TEXT,
  general_procedure TEXT,
  processing_time VARCHAR(50),
  is_active TINYINT DEFAULT 1,
  FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
);

-- Payment Methods Table (Admin Managed) — must be before request_quotes
CREATE TABLE payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('bank', 'payment_link') NOT NULL,
  label VARCHAR(100) NOT NULL,
  bank_name VARCHAR(100) DEFAULT NULL,
  account_title VARCHAR(100) DEFAULT NULL,
  account_number VARCHAR(100) DEFAULT NULL,
  iban VARCHAR(100) DEFAULT NULL,
  payment_url TEXT DEFAULT NULL,
  is_active TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Service Requests Table
CREATE TABLE requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  agent_id INT DEFAULT NULL,
  country_id INT NOT NULL,
  visa_type_id INT NOT NULL,
  status ENUM(
    'pending',
    'quote_sent',
    'customer_approved',
    'customer_declined',
    'payment_uploaded',
    'manager_approved',
    'manager_rejected',
    'completed'
  ) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES users(id),
  FOREIGN KEY (agent_id) REFERENCES users(id)
);

-- Agent Quote Details Table
CREATE TABLE request_quotes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT UNIQUE NOT NULL,
  service_price DECIMAL(10,2),
  payment_mode VARCHAR(100),
  payment_method_id INT,
  installment_plan TEXT,
  processing_time VARCHAR(100),
  special_notes TEXT,
  FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE,
  FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
);

-- Customer Signature Table
CREATE TABLE request_signatures (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT UNIQUE NOT NULL,
  signature_data LONGTEXT NOT NULL,
  signed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE
);

-- Payment Proof Table
CREATE TABLE payment_proofs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE
);

-- SMTP Settings Table
CREATE TABLE smtp_settings (
  id INT PRIMARY KEY DEFAULT 1,
  host VARCHAR(100),
  port INT DEFAULT 587,
  username VARCHAR(100),
  password VARCHAR(255),
  encryption ENUM('ssl', 'tls') DEFAULT 'tls',
  from_name VARCHAR(100),
  from_email VARCHAR(100)
);

-- Generated Documents Table
CREATE TABLE documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  type ENUM('invoice', 'contract') NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE
);

-- Notifications Table
CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(200),
  message TEXT,
  is_read TINYINT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Messages / Chat Table
CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sender_id INT NOT NULL,
  receiver_id INT NOT NULL,
  message TEXT NOT NULL,
  is_read TINYINT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
);