-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Tier 1 · Master data (shared across all modules)
-- customers, customers_branches, vendors, budget_item_categories,
-- chart_of_accounts, bank_accounts
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS bank_accounts;
DROP TABLE IF EXISTS chart_of_accounts;
DROP TABLE IF EXISTS budget_item_categories;
DROP TABLE IF EXISTS customers_branches;
DROP TABLE IF EXISTS vendors;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_no       VARCHAR(20) NOT NULL DEFAULT '',
  customer_code     VARCHAR(10) NOT NULL DEFAULT '',
  fname             VARCHAR(120) NOT NULL,
  lname             VARCHAR(120) NOT NULL DEFAULT '',
  email             VARCHAR(120) NOT NULL DEFAULT '',
  phone_1           VARCHAR(40) NOT NULL DEFAULT '',
  phone_2           VARCHAR(40) NOT NULL DEFAULT '',
  physical_address  TEXT,
  po_box            VARCHAR(40) NOT NULL DEFAULT '',
  city              VARCHAR(60) NOT NULL DEFAULT '',
  country           VARCHAR(60) NOT NULL DEFAULT 'TANZANIA',
  customer_tin      VARCHAR(40) NOT NULL DEFAULT '',
  customer_vrn      VARCHAR(40) NOT NULL DEFAULT '',
  customer_sector   VARCHAR(60) NOT NULL DEFAULT '',
  customer_tier     VARCHAR(20) NOT NULL DEFAULT 'RETAIL', -- RETAIL|WHOLESALE|GOVERNMENT|STRATEGIC
  customer_poc      VARCHAR(120) NOT NULL DEFAULT '',
  customer_poc_role VARCHAR(60) NOT NULL DEFAULT '',
  customer_poc_phone VARCHAR(40) NOT NULL DEFAULT '',
  customer_poc_email VARCHAR(120) NOT NULL DEFAULT '',
  status            VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  created_by        INT NOT NULL DEFAULT 0,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by         INT NOT NULL DEFAULT 0,
  edited_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted           TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_customer_no (customer_no),
  KEY ix_sector (customer_sector),
  KEY ix_tier   (customer_tier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE customers_branches (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id       INT NOT NULL,
  branch_name       VARCHAR(120) NOT NULL,
  branch_address    TEXT,
  region            VARCHAR(80) NOT NULL DEFAULT '',
  district          VARCHAR(80) NOT NULL DEFAULT '',
  branch_poc        VARCHAR(120) NOT NULL DEFAULT '',
  branch_poc_phone  VARCHAR(40) NOT NULL DEFAULT '',
  created_by        INT NOT NULL DEFAULT 0,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by         INT NOT NULL DEFAULT 0,
  edited_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted           TINYINT(1) NOT NULL DEFAULT 0,
  KEY ix_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE vendors (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  vendor_no         VARCHAR(20) NOT NULL DEFAULT '',
  vendor_name       VARCHAR(160) NOT NULL,
  vendor_address    TEXT,
  vendor_phone_1    VARCHAR(40) NOT NULL DEFAULT '',
  vendor_phone_2    VARCHAR(40) NOT NULL DEFAULT '',
  vendor_email      VARCHAR(120) NOT NULL DEFAULT '',
  vendor_website    VARCHAR(200) NOT NULL DEFAULT '',
  vendor_po_box     VARCHAR(40) NOT NULL DEFAULT '',
  vendor_city       VARCHAR(60) NOT NULL DEFAULT '',
  vendor_country    VARCHAR(60) NOT NULL DEFAULT 'TANZANIA',
  vendor_tin        VARCHAR(40) NOT NULL DEFAULT '',
  vendor_vrn        VARCHAR(40) NOT NULL DEFAULT '',
  vendor_category   VARCHAR(60) NOT NULL DEFAULT '', -- goods|services|works (or blank = any)
  vendor_poc        VARCHAR(120) NOT NULL DEFAULT '',
  vendor_poc_phone  VARCHAR(40) NOT NULL DEFAULT '',
  vendor_poc_email  VARCHAR(120) NOT NULL DEFAULT '',
  bank_name         VARCHAR(120) NOT NULL DEFAULT '',
  bank_account_no   VARCHAR(60) NOT NULL DEFAULT '',
  bank_swift        VARCHAR(40) NOT NULL DEFAULT '',
  status            VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  created_by        INT NOT NULL DEFAULT 0,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by         INT NOT NULL DEFAULT 0,
  edited_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted           TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_vendor_no (vendor_no),
  KEY ix_category (vendor_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE budget_item_categories (
  c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(120) NOT NULL,
  category_code VARCHAR(20)  NOT NULL DEFAULT '',
  description   TEXT,
  parent_id     INT NOT NULL DEFAULT 0,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_code (category_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE chart_of_accounts (
  c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  account_code  VARCHAR(20) NOT NULL,
  account_name  VARCHAR(160) NOT NULL,
  account_type  VARCHAR(20) NOT NULL, -- ASSET|LIABILITY|EQUITY|REVENUE|EXPENSE
  parent_id     INT NOT NULL DEFAULT 0,
  is_bank       TINYINT(1) NOT NULL DEFAULT 0,
  is_control    TINYINT(1) NOT NULL DEFAULT 0,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  currency      VARCHAR(10) NOT NULL DEFAULT 'TZS',
  created_by    INT NOT NULL DEFAULT 0,
  created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_account_code (account_code),
  KEY ix_type (account_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bank_accounts (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  account_name     VARCHAR(120) NOT NULL,
  bank_name        VARCHAR(120) NOT NULL,
  account_number   VARCHAR(60)  NOT NULL,
  swift_code       VARCHAR(40)  NOT NULL DEFAULT '',
  currency         VARCHAR(10)  NOT NULL DEFAULT 'TZS',
  coa_id           INT NOT NULL DEFAULT 0,
  is_active        TINYINT(1) NOT NULL DEFAULT 1,
  opening_balance  DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  opening_on       DATE DEFAULT NULL,
  created_by       INT NOT NULL DEFAULT 0,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_bank_acct (bank_name, account_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
