-- Migration: create core application schema
-- Tables: users, projects, activities, sub_activities, project_assignments,
-- bill_of_quantities, procurement_plans, requisition, requisition_items,
-- workflow_assignments, quotations, invoices, supplier_scores, requests

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  name VARCHAR(255) DEFAULT NULL,
  role VARCHAR(50) DEFAULT 'requester',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT DEFAULT NULL,
  budget DECIMAL(15,2) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  budget DECIMAL(15,2) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sub_activities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  activity_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  budget DECIMAL(15,2) DEFAULT 0,
  INDEX (activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  requester_email VARCHAR(255) NOT NULL,
  INDEX (project_id),
  INDEX (requester_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bill_of_quantities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  boq_name VARCHAR(255) NOT NULL,
  project_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS procurement_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS requisition (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  requester_email VARCHAR(255) NOT NULL,
  requisition_type VARCHAR(50) DEFAULT NULL,
  boq_id INT DEFAULT NULL,
  procurement_plan_id INT DEFAULT NULL,
  justification TEXT DEFAULT NULL,
  status VARCHAR(50) DEFAULT 'Draft',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME DEFAULT NULL,
  INDEX (requester_email),
  INDEX (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS requisition_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requisition_id INT NOT NULL,
  source_item_id INT DEFAULT NULL,
  requested_qty DECIMAL(12,2) DEFAULT 0,
  total_cost DECIMAL(15,2) DEFAULT 0,
  INDEX (requisition_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workflow_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requisition_id INT NOT NULL,
  assigned_to VARCHAR(255) DEFAULT NULL,
  assigned_role VARCHAR(100) NOT NULL,
  assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  due_at DATETIME DEFAULT NULL,
  is_active TINYINT(1) DEFAULT 1,
  INDEX (requisition_id),
  INDEX (assigned_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quotations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requisition_id INT NOT NULL,
  supplier_name VARCHAR(255) NOT NULL,
  file VARCHAR(255) NOT NULL,
  uploaded_by VARCHAR(255) DEFAULT NULL,
  uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  is_selected TINYINT(1) DEFAULT 0,
  INDEX (requisition_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requisition_id INT NOT NULL,
  invoice_number VARCHAR(255) NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  file VARCHAR(255) DEFAULT NULL,
  uploaded_by VARCHAR(255) DEFAULT NULL,
  uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (requisition_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS supplier_scores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quotation_id INT NOT NULL,
  price INT DEFAULT 0,
  delivery INT DEFAULT 0,
  warranty INT DEFAULT 0,
  compliance INT DEFAULT 0,
  total_score INT DEFAULT 0,
  INDEX (quotation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requester_email VARCHAR(255) NOT NULL,
  activity_id INT DEFAULT NULL,
  sub_activity_id INT DEFAULT NULL,
  amount DECIMAL(15,2) DEFAULT 0,
  status VARCHAR(50) DEFAULT 'Pending',
  payment_method VARCHAR(50) DEFAULT NULL,
  payment_provider VARCHAR(255) DEFAULT NULL,
  account_number VARCHAR(255) DEFAULT NULL,
  phone_number VARCHAR(50) DEFAULT NULL,
  accountant_comment TEXT DEFAULT NULL,
  transaction_ref VARCHAR(255) DEFAULT NULL,
  paid_at DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (requester_email),
  INDEX (activity_id),
  INDEX (sub_activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;