-- Invoice System – MySQL tables (import in phpMyAdmin: select your database > Import).
-- After importing, open install.php once to create the admin login.

CREATE TABLE IF NOT EXISTS companies (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  overdue_days INT NOT NULL DEFAULT 30,
  UNIQUE KEY uq_company_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL,
  pass VARCHAR(255) NOT NULL,
  role VARCHAR(10) NOT NULL DEFAULT 'user',
  status VARCHAR(10) NOT NULL DEFAULT 'active',
  selfreg TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_companies (
  user_id INT UNSIGNED NOT NULL,
  company_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, company_id),
  CONSTRAINT fk_uc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_uc_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  `date` VARCHAR(20) NOT NULL DEFAULT '',
  inv VARCHAR(100) NOT NULL,
  shop VARCHAR(255) NOT NULL DEFAULT '',
  amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  rep VARCHAR(255) NOT NULL DEFAULT '',
  `type` VARCHAR(10) NOT NULL DEFAULT '',
  cash DECIMAL(15,2) NOT NULL DEFAULT 0,
  cheque DECIMAL(15,2) NOT NULL DEFAULT 0,
  chqno VARCHAR(60) NOT NULL DEFAULT '',
  cdate VARCHAR(20) NOT NULL DEFAULT '',
  bank VARCHAR(100) NOT NULL DEFAULT '',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_company_inv (company_id, inv),
  KEY ix_company_date (company_id, `date`),
  CONSTRAINT fk_inv_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_fails (
  ip VARCHAR(45) NOT NULL,
  t INT UNSIGNED NOT NULL,
  KEY ix_ip (ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;