-- DMS-CRM Database Schema
-- Complete schema for all entities

-- ============================================
-- CORE TABLES
-- ============================================

-- Users table (already created by seed, but included for completeness)
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL CHECK (role IN ('Super Admin', 'Admin', 'Agent')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Pipelines
CREATE TABLE IF NOT EXISTS pipelines (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  is_active BOOLEAN DEFAULT true,
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Pipeline Stages
CREATE TABLE IF NOT EXISTS stages (
  id SERIAL PRIMARY KEY,
  pipeline_id INTEGER REFERENCES pipelines(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  display_order INTEGER NOT NULL,
  color VARCHAR(7),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- LEAD MANAGEMENT
-- ============================================

-- Leads (with unique 8-digit lead_id)
CREATE TABLE IF NOT EXISTS leads (
  id SERIAL PRIMARY KEY,
  lead_id VARCHAR(8) UNIQUE NOT NULL,
  file_number VARCHAR(8) UNIQUE,
  
  -- Lead source and assignment
  source VARCHAR(100),
  assigned_to INTEGER REFERENCES users(id),
  pipeline_id INTEGER REFERENCES pipelines(id),
  stage_id INTEGER REFERENCES stages(id),
  
  -- Lead type
  lead_type VARCHAR(50) CHECK (lead_type IN ('Home Loan', 'Car Loan', 'Personal Loan', 'Business Loan', 'Other')),
  
  -- Property/Asset details
  property_type VARCHAR(100),
  property_value DECIMAL(15, 2),
  property_address TEXT,
  
  -- Loan details
  loan_amount DECIMAL(15, 2),
  loan_tenure INTEGER,
  interest_rate DECIMAL(5, 2),
  
  -- Referee and POC
  referee_name VARCHAR(255),
  referee_phone VARCHAR(20),
  poc_name VARCHAR(255),
  poc_phone VARCHAR(20),
  poc_email VARCHAR(255),
  
  -- Status
  status VARCHAR(50) DEFAULT 'New',
  priority VARCHAR(20) CHECK (priority IN ('Low', 'Medium', 'High', 'Urgent')),
  
  -- Timestamps
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Applicants (Primary)
CREATE TABLE IF NOT EXISTS applicants (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  -- Personal details
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(20) NOT NULL,
  alternate_phone VARCHAR(20),
  date_of_birth DATE,
  gender VARCHAR(20),
  marital_status VARCHAR(50),
  
  -- Address
  current_address TEXT,
  permanent_address TEXT,
  city VARCHAR(100),
  state VARCHAR(100),
  pincode VARCHAR(10),
  
  -- Employment
  employment_type VARCHAR(100),
  employer_name VARCHAR(255),
  designation VARCHAR(255),
  monthly_income DECIMAL(15, 2),
  work_experience_years INTEGER,
  
  -- Identity
  pan_number VARCHAR(10),
  aadhar_number VARCHAR(12),
  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Co-Applicants
CREATE TABLE IF NOT EXISTS co_applicants (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  -- Personal details
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(20),
  date_of_birth DATE,
  gender VARCHAR(20),
  relationship VARCHAR(100),
  
  -- Employment
  employment_type VARCHAR(100),
  employer_name VARCHAR(255),
  monthly_income DECIMAL(15, 2),
  
  -- Identity
  pan_number VARCHAR(10),
  aadhar_number VARCHAR(12),
  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- DOCUMENTS & FILES
-- ============================================

-- Documents
CREATE TABLE IF NOT EXISTS documents (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  document_type VARCHAR(100) NOT NULL,
  document_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_size BIGINT,
  mime_type VARCHAR(100),
  
  uploaded_by INTEGER REFERENCES users(id),
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  is_verified BOOLEAN DEFAULT false,
  verified_by INTEGER REFERENCES users(id),
  verified_at TIMESTAMP
);

-- Voice Notes
CREATE TABLE IF NOT EXISTS voice_notes (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  file_path VARCHAR(500) NOT NULL,
  duration_seconds INTEGER,
  file_size BIGINT,
  
  transcription TEXT,
  
  recorded_by INTEGER REFERENCES users(id),
  recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Files (General file storage)
CREATE TABLE IF NOT EXISTS files (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_size BIGINT,
  mime_type VARCHAR(100),
  category VARCHAR(100),
  
  uploaded_by INTEGER REFERENCES users(id),
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- ACTIVITIES & TASKS
-- ============================================

-- Activities (Timeline)
CREATE TABLE IF NOT EXISTS activities (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  activity_type VARCHAR(100) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  
  performed_by INTEGER REFERENCES users(id),
  performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tasks
CREATE TABLE IF NOT EXISTS tasks (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  title VARCHAR(255) NOT NULL,
  description TEXT,
  due_date TIMESTAMP,
  priority VARCHAR(20) CHECK (priority IN ('Low', 'Medium', 'High', 'Urgent')),
  status VARCHAR(50) DEFAULT 'Pending' CHECK (status IN ('Pending', 'In Progress', 'Completed', 'Cancelled')),
  
  assigned_to INTEGER REFERENCES users(id),
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP
);

-- ============================================
-- FINANCIAL TRACKING
-- ============================================

-- Bank Movements
CREATE TABLE IF NOT EXISTS bank_movements (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  transaction_type VARCHAR(100) NOT NULL,
  amount DECIMAL(15, 2) NOT NULL,
  transaction_date DATE NOT NULL,
  bank_name VARCHAR(255),
  account_number VARCHAR(50),
  reference_number VARCHAR(100),
  description TEXT,
  
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sanctions
CREATE TABLE IF NOT EXISTS sanctions (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  sanctioned_amount DECIMAL(15, 2) NOT NULL,
  sanction_date DATE NOT NULL,
  sanction_letter_path VARCHAR(500),
  bank_name VARCHAR(255),
  loan_account_number VARCHAR(50),
  
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- DSAs (Direct Selling Agents)
CREATE TABLE IF NOT EXISTS dsas (
  id SERIAL PRIMARY KEY,
  
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(20) NOT NULL,
  company_name VARCHAR(255),
  commission_rate DECIMAL(5, 2),
  
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Payouts
CREATE TABLE IF NOT EXISTS payouts (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  dsa_id INTEGER REFERENCES dsas(id),
  
  amount DECIMAL(15, 2) NOT NULL,
  payout_date DATE,
  payment_method VARCHAR(100),
  transaction_reference VARCHAR(100),
  status VARCHAR(50) DEFAULT 'Pending' CHECK (status IN ('Pending', 'Processed', 'Completed', 'Failed')),
  
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Pendings
CREATE TABLE IF NOT EXISTS pendings (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  pending_type VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  due_date DATE,
  status VARCHAR(50) DEFAULT 'Open' CHECK (status IN ('Open', 'In Progress', 'Resolved', 'Closed')),
  
  assigned_to INTEGER REFERENCES users(id),
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  resolved_at TIMESTAMP
);

-- ============================================
-- AUDIT & TRACKING
-- ============================================

-- Audit Logs
CREATE TABLE IF NOT EXISTS audit_logs (
  id SERIAL PRIMARY KEY,
  
  table_name VARCHAR(100) NOT NULL,
  record_id INTEGER NOT NULL,
  action VARCHAR(50) NOT NULL CHECK (action IN ('CREATE', 'UPDATE', 'DELETE')),
  
  old_values JSONB,
  new_values JSONB,
  
  performed_by INTEGER REFERENCES users(id),
  performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ip_address VARCHAR(45)
);

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

CREATE INDEX IF NOT EXISTS idx_leads_lead_id ON leads(lead_id);
CREATE INDEX IF NOT EXISTS idx_leads_file_number ON leads(file_number);
CREATE INDEX IF NOT EXISTS idx_leads_assigned_to ON leads(assigned_to);
CREATE INDEX IF NOT EXISTS idx_leads_stage_id ON leads(stage_id);
CREATE INDEX IF NOT EXISTS idx_leads_created_at ON leads(created_at);

CREATE INDEX IF NOT EXISTS idx_applicants_lead_id ON applicants(lead_id);
CREATE INDEX IF NOT EXISTS idx_documents_lead_id ON documents(lead_id);
CREATE INDEX IF NOT EXISTS idx_activities_lead_id ON activities(lead_id);
CREATE INDEX IF NOT EXISTS idx_tasks_lead_id ON tasks(lead_id);
CREATE INDEX IF NOT EXISTS idx_tasks_assigned_to ON tasks(assigned_to);

CREATE INDEX IF NOT EXISTS idx_audit_logs_table_record ON audit_logs(table_name, record_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_performed_at ON audit_logs(performed_at);

-- ============================================
-- FUNCTIONS FOR 8-DIGIT ID GENERATION
-- ============================================

-- Function to generate 8-digit lead_id
CREATE OR REPLACE FUNCTION generate_lead_id() RETURNS VARCHAR(8) AS $$
DECLARE
  new_id VARCHAR(8);
  max_id INTEGER;
BEGIN
  -- Get the maximum numeric lead_id
  SELECT COALESCE(MAX(CAST(lead_id AS INTEGER)), 10000000) INTO max_id FROM leads WHERE lead_id ~ '^[0-9]+$';
  
  -- Increment and format as 8-digit string
  new_id := LPAD((max_id + 1)::TEXT, 8, '0');
  
  RETURN new_id;
END;
$$ LANGUAGE plpgsql;

-- Function to generate 8-digit file_number
CREATE OR REPLACE FUNCTION generate_file_number() RETURNS VARCHAR(8) AS $$
DECLARE
  new_id VARCHAR(8);
  max_id INTEGER;
BEGIN
  -- Get the maximum numeric file_number
  SELECT COALESCE(MAX(CAST(file_number AS INTEGER)), 20000000) INTO max_id FROM leads WHERE file_number ~ '^[0-9]+$' AND file_number IS NOT NULL;
  
  -- Increment and format as 8-digit string
  new_id := LPAD((max_id + 1)::TEXT, 8, '0');
  
  RETURN new_id;
END;
$$ LANGUAGE plpgsql;
