SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;




CREATE TABLE estates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  location VARCHAR(255) NOT NULL,
  total_plots INT NOT NULL,
  available_plots INT NOT NULL,
  default_plot_price DECIMAL(12,2) NOT NULL,
  default_plot_size VARCHAR(50) NOT NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;




CREATE TABLE clients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fullname VARCHAR(150) NOT NULL,
  email VARCHAR(150) UNIQUE NULL,
  phone VARCHAR(50) NOT NULL UNIQUE,
  dob DATE NULL,
  occupation VARCHAR(150) NULL,
  source_of_income VARCHAR(150) NULL,
  address TEXT NULL,

  next_of_kin_name VARCHAR(150) NULL,
  next_of_kin_phone VARCHAR(50) NULL,
  next_of_kin_relationship VARCHAR(100) NULL,
  next_of_kin_address TEXT NULL,

  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;





CREATE TABLE admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fullname VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(50) DEFAULT 'admin',
  status ENUM('active','inactive') DEFAULT 'active',
  last_login DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;






CREATE TABLE staff (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fullname VARCHAR(150) NOT NULL,
  state VARCHAR(150),
  lga VARCHAR(150),
  address VARCHAR(200),
  phone VARCHAR(50) NOT NULL,
  email VARCHAR(150) NULL,
  position VARCHAR(255),
  guarantor_name VARCHAR(150),
  guarantor_phone VARCHAR(150),
  guarantor_address VARCHAR(255),
  guarantor_two_name VARCHAR(150),
  guarantor_two_phone VARCHAR(150),
  guarantor_two_address VARCHAR(255),  
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;





CREATE TABLE plots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  estate_id BIGINT UNSIGNED NOT NULL,
  plot_number VARCHAR(50) NOT NULL,
  plot_size VARCHAR(50) NOT NULL,
  status ENUM('available','sold','reserved_company','reserved_client') DEFAULT 'available',

  current_owner_id BIGINT UNSIGNED NULL,
  current_sale_id BIGINT UNSIGNED NULL,
  reserved_for_client_id BIGINT UNSIGNED NULL,

  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_estate_plot (estate_id, plot_number),
  INDEX idx_estate_status (estate_id, status),

  CONSTRAINT fk_plot_estate FOREIGN KEY (estate_id) REFERENCES estates(id),
  CONSTRAINT fk_plot_owner FOREIGN KEY (current_owner_id) REFERENCES clients(id),
  CONSTRAINT fk_plot_sale FOREIGN KEY (current_sale_id) REFERENCES sales(id),
  CONSTRAINT fk_plot_reserved_client FOREIGN KEY (reserved_for_client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

NOTE = The fk_plot_sale will be added after sales is created





CREATE TABLE sales (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  estate_id BIGINT UNSIGNED NOT NULL,

  sale_type ENUM('instant','installment') NOT NULL,
  number_of_plots INT NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,

  total_amount DECIMAL(14,2) NOT NULL,
  total_paid DECIMAL(14,2) DEFAULT 0,
  balance_due DECIMAL(14,2) NOT NULL,

  status ENUM('pending','completed','cancelled') DEFAULT 'pending',

  documents_required_json TEXT NULL,
  documents_collected_flag TINYINT(1) DEFAULT 0,

  realtor_id BIGINT UNSIGNED NULL,
  created_by_admin_id BIGINT UNSIGNED NOT NULL,

  sale_date DATE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX (client_id),
  INDEX (estate_id),
  INDEX (status),

  CONSTRAINT fk_sale_client FOREIGN KEY (client_id) REFERENCES clients(id),
  CONSTRAINT fk_sale_estate FOREIGN KEY (estate_id) REFERENCES estates(id),
  CONSTRAINT fk_sale_realtor FOREIGN KEY (realtor_id) REFERENCES staff(id),
  CONSTRAINT fk_sale_admin FOREIGN KEY (created_by_admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;






CREATE TABLE sale_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_id BIGINT UNSIGNED NOT NULL,
  plot_id BIGINT UNSIGNED NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_sale_plot (sale_id, plot_id),

  CONSTRAINT fk_sale_item_sale FOREIGN KEY (sale_id) REFERENCES sales(id),
  CONSTRAINT fk_sale_item_plot FOREIGN KEY (plot_id) REFERENCES plots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;






CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  payment_method VARCHAR(50) NOT NULL,
  payment_date DATE NOT NULL,
  recorded_by_admin_id BIGINT UNSIGNED NOT NULL,
  notes TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX (sale_id),

  CONSTRAINT fk_payment_sale FOREIGN KEY (sale_id) REFERENCES sales(id),
  CONSTRAINT fk_payment_admin FOREIGN KEY (recorded_by_admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;






CREATE TABLE payment_schedules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_id BIGINT UNSIGNED NOT NULL,
  due_date DATE NOT NULL,
  amount_due DECIMAL(14,2) NOT NULL,
  status ENUM('pending','paid') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX (sale_id),

  CONSTRAINT fk_schedule_sale FOREIGN KEY (sale_id) REFERENCES sales(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;







CREATE TABLE plot_ownerships (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plot_id BIGINT UNSIGNED NOT NULL,
  sale_id BIGINT UNSIGNED NOT NULL,
  from_client_id BIGINT UNSIGNED NULL,
  to_client_id BIGINT UNSIGNED NULL,
  transfer_type ENUM('initial_sale','resale','reversal') NOT NULL,
  transfer_date DATETIME NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX (plot_id),
  INDEX (sale_id),

  CONSTRAINT fk_owner_plot FOREIGN KEY (plot_id) REFERENCES plots(id),
  CONSTRAINT fk_owner_sale FOREIGN KEY (sale_id) REFERENCES sales(id),
  CONSTRAINT fk_owner_from FOREIGN KEY (from_client_id) REFERENCES clients(id),
  CONSTRAINT fk_owner_to FOREIGN KEY (to_client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;







CREATE TABLE document_types (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_doc_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;






CREATE TABLE sale_required_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_id BIGINT UNSIGNED NOT NULL,
  document_type_id BIGINT UNSIGNED NOT NULL,
  is_collected TINYINT(1) DEFAULT 0,
  collected_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_sale_doc (sale_id, document_type_id),

  CONSTRAINT fk_req_sale FOREIGN KEY (sale_id) REFERENCES sales(id),
  CONSTRAINT fk_req_doc FOREIGN KEY (document_type_id) REFERENCES document_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;







CREATE TABLE sale_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_id BIGINT UNSIGNED NOT NULL,
  document_type_id BIGINT UNSIGNED NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_type ENUM('pdf','jpg','png','webp') NOT NULL,
  uploaded_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX (sale_id),

  CONSTRAINT fk_doc_sale FOREIGN KEY (sale_id) REFERENCES sales(id),
  CONSTRAINT fk_doc_type FOREIGN KEY (document_type_id) REFERENCES document_types(id),
  CONSTRAINT fk_doc_admin FOREIGN KEY (uploaded_by_admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;






CREATE TABLE expenses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  description TEXT NULL,
  amount DECIMAL(14,2) NOT NULL,
  expense_date DATE NOT NULL,
  recorded_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_expense_admin FOREIGN KEY (recorded_by_admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;




CREATE TABLE income (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  description TEXT NULL,
  amount DECIMAL(14,2) NOT NULL,

  income_source VARCHAR(150) NULL, 
  related_sale_id BIGINT UNSIGNED NULL,

  income_date DATE NOT NULL,
  recorded_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX (income_date),
  INDEX (related_sale_id),

  CONSTRAINT fk_income_admin 
    FOREIGN KEY (recorded_by_admin_id) REFERENCES admins(id),

  CONSTRAINT fk_income_sale 
    FOREIGN KEY (related_sale_id) REFERENCES sales(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;






ALTER TABLE plots
  ADD CONSTRAINT fk_plot_sale
  FOREIGN KEY (current_sale_id) REFERENCES sales(id);



SET FOREIGN_KEY_CHECKS = 1;