-- Divar Ferry Pass App - Database Schema
-- Engine: InnoDB | Charset: utf8mb4 | Timezone handled in PHP/session as Asia/Kolkata
-- Naming: lowercase, no underscores between words

SET NAMES utf8mb4;
SET time_zone = '+05:30';

CREATE DATABASE IF NOT EXISTS divarferrypass CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE divarferrypass;

-- ---------------------------------------------------------------
-- panchayat (master)
-- ---------------------------------------------------------------
CREATE TABLE panchayat (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(30) NOT NULL,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uqcode (code)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- villager
-- ---------------------------------------------------------------
CREATE TABLE villager (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fullname VARCHAR(150) NOT NULL,
  photo VARCHAR(255) NULL,
  phone VARCHAR(15) NOT NULL,
  apitoken VARCHAR(191) NULL,
  hasaadhaar TINYINT(1) NOT NULL DEFAULT 0,
  aadhaarnumber VARCHAR(20) NULL,
  aadhaarphoto VARCHAR(255) NULL,
  verifymethod VARCHAR(20) NULL COMMENT 'aadhaar or house',
  address VARCHAR(255) NULL,
  houseno VARCHAR(50) NULL,
  village VARCHAR(100) NULL COMMENT 'Aadhaar vtc field - used to auto-detect panchayat via villagepanchayat table',
  panchayatid INT NULL,
  dob DATE NULL,
  age INT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, approved, rejected',
  rejectreason VARCHAR(255) NULL,
  approvedby INT NULL COMMENT 'panchayatuser.id',
  approvedat DATETIME NULL,
  registrationfee DECIMAL(10,2) NULL,
  paymentstatus VARCHAR(20) NOT NULL DEFAULT 'unpaid' COMMENT 'unpaid, paid',
  paidat DATETIME NULL,
  passstartdate DATE NULL,
  passenddate DATE NULL,
  passactive TINYINT(1) NOT NULL DEFAULT 0,
  deactivatedby INT NULL,
  deactivatedat DATETIME NULL,
  deactivatereason VARCHAR(255) NULL,
  barcodetext VARCHAR(100) NULL,
  isprinted TINYINT(1) NOT NULL DEFAULT 0,
  printedat DATETIME NULL,
  printedby INT NULL COMMENT 'rnduser.id',
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uqphone (phone),
  UNIQUE KEY uqbarcode (barcodetext),
  KEY idxpanchayat (panchayatid),
  KEY idxstatus (status),
  CONSTRAINT fkvillagerpanchayat FOREIGN KEY (panchayatid) REFERENCES panchayat(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- villagepanchayat (maps an Aadhaar village name - the 'vtc' QR field - to a panchayat.
-- A panchayat can cover more than one village, but each village maps to exactly one
-- panchayat. Matching is case-insensitive against the stored villagename.)
-- ---------------------------------------------------------------
CREATE TABLE villagepanchayat (
  id INT AUTO_INCREMENT PRIMARY KEY,
  villagename VARCHAR(100) NOT NULL,
  panchayatid INT NOT NULL,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uqvillagename (villagename),
  CONSTRAINT fkvillagepanchayat FOREIGN KEY (panchayatid) REFERENCES panchayat(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- villagervehicle
-- ---------------------------------------------------------------
CREATE TABLE villagervehicle (
  id INT AUTO_INCREMENT PRIMARY KEY,
  villagerid INT NOT NULL,
  vehiclenumber VARCHAR(30) NOT NULL,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idxvillager (villagerid),
  CONSTRAINT fkvehiclevillager FOREIGN KEY (villagerid) REFERENCES villager(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- panchayatuser (portal login)
-- ---------------------------------------------------------------
CREATE TABLE panchayatuser (
  id INT AUTO_INCREMENT PRIMARY KEY,
  panchayatid INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  username VARCHAR(100) NOT NULL,
  phone VARCHAR(15) NULL,
  password VARCHAR(64) NOT NULL COMMENT 'sha256',
  role VARCHAR(30) NOT NULL DEFAULT 'panchayatuser',
  isactive TINYINT(1) NOT NULL DEFAULT 1,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uqusername (username),
  KEY idxpanchayat (panchayatid),
  CONSTRAINT fkpanchayatuserpanchayat FOREIGN KEY (panchayatid) REFERENCES panchayat(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- rnduser (portal login)
-- ---------------------------------------------------------------
CREATE TABLE rnduser (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  username VARCHAR(100) NOT NULL,
  phone VARCHAR(15) NULL,
  password VARCHAR(64) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'rnduser',
  isactive TINYINT(1) NOT NULL DEFAULT 1,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uqusername (username)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- adminuser (portal login)
-- ---------------------------------------------------------------
CREATE TABLE adminuser (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  username VARCHAR(100) NOT NULL,
  phone VARCHAR(15) NULL,
  password VARCHAR(64) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'admin',
  isactive TINYINT(1) NOT NULL DEFAULT 1,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uqusername (username)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- ticketchecker (app login)
-- ---------------------------------------------------------------
CREATE TABLE ticketchecker (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(15) NOT NULL,
  username VARCHAR(100) NULL,
  password VARCHAR(64) NOT NULL,
  apitoken VARCHAR(191) NULL,
  panchayatid INT NULL,
  isactive TINYINT(1) NOT NULL DEFAULT 1,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uqphone (phone),
  CONSTRAINT fkcheckerpanchayat FOREIGN KEY (panchayatid) REFERENCES panchayat(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- ridelog (scan history)
-- ---------------------------------------------------------------
CREATE TABLE ridelog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  villagerid INT NULL,
  checkerid INT NULL,
  scandatetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  result VARCHAR(20) NOT NULL COMMENT 'valid, expired, inactive, invalid',
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idxvillager (villagerid),
  KEY idxchecker (checkerid),
  CONSTRAINT fkridelogvillager FOREIGN KEY (villagerid) REFERENCES villager(id),
  CONSTRAINT fkridelogchecker FOREIGN KEY (checkerid) REFERENCES ticketchecker(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- paymentorder (razorpay orders - auto & admin created)
-- ---------------------------------------------------------------
CREATE TABLE paymentorder (
  id INT AUTO_INCREMENT PRIMARY KEY,
  villagerid INT NOT NULL,
  razorpayorderid VARCHAR(100) NULL,
  razorpaypaymentid VARCHAR(100) NULL,
  razorpaysignature VARCHAR(255) NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'INR',
  status VARCHAR(20) NOT NULL DEFAULT 'created' COMMENT 'created, paid, failed, cancelled',
  paymentmethod VARCHAR(20) NOT NULL DEFAULT 'online' COMMENT 'online, cash, upi, free',
  createdbytype VARCHAR(20) NOT NULL DEFAULT 'system' COMMENT 'system, admin, panchayat',
  createdbyid INT NULL COMMENT 'adminuser.id when createdbytype=admin',
  paylink VARCHAR(255) NULL,
  paidat DATETIME NULL,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idxvillager (villagerid),
  KEY idxrzporderid (razorpayorderid),
  KEY idxstatus (status),
  CONSTRAINT fkpaymentordervillager FOREIGN KEY (villagerid) REFERENCES villager(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- paymentwebhooklog (raw razorpay webhook payloads)
-- ---------------------------------------------------------------
CREATE TABLE paymentwebhooklog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  razorpayorderid VARCHAR(100) NULL,
  razorpaypaymentid VARCHAR(100) NULL,
  eventtype VARCHAR(50) NULL,
  payload LONGTEXT NULL,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  receivedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idxrzporderid (razorpayorderid)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- otp (login / forgot password / registration)
-- ---------------------------------------------------------------
CREATE TABLE otp (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mobileoremail VARCHAR(100) NOT NULL,
  otp VARCHAR(10) NOT NULL,
  purpose VARCHAR(30) NOT NULL COMMENT 'register, login, forgotpassword',
  isused TINYINT(1) NOT NULL DEFAULT 0,
  expiresat DATETIME NOT NULL,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idxmobileoremail (mobileoremail)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- setting (key-value config)
-- ---------------------------------------------------------------
CREATE TABLE setting (
  id INT AUTO_INCREMENT PRIMARY KEY,
  settingkey VARCHAR(60) NOT NULL,
  settingvalue VARCHAR(255) NOT NULL,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uqsettingkey (settingkey)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- activitylog (audit trail)
-- ---------------------------------------------------------------
CREATE TABLE activitylog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  entitytype VARCHAR(50) NOT NULL COMMENT 'villager, paymentorder, panchayatuser, etc',
  entityid INT NOT NULL,
  action VARCHAR(50) NOT NULL COMMENT 'approve, reject, deactivate, renew, createorder, etc',
  actionbyrole VARCHAR(30) NOT NULL,
  actionbyid INT NULL,
  remarks VARCHAR(255) NULL,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idxentity (entitytype, entityid)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- deleteaccountrequest
-- ---------------------------------------------------------------
CREATE TABLE deleteaccountrequest (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mobileoremail VARCHAR(100) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, processed',
  remarks VARCHAR(255) NULL,
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updatedat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- smslog (stub log)
-- ---------------------------------------------------------------
CREATE TABLE smslog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mobile VARCHAR(15) NOT NULL,
  message VARCHAR(500) NOT NULL,
  purpose VARCHAR(30) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'stubbed',
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- whatsapplog (stub log)
-- ---------------------------------------------------------------
CREATE TABLE whatsapplog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mobile VARCHAR(15) NOT NULL,
  message VARCHAR(500) NOT NULL,
  purpose VARCHAR(30) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'stubbed',
  isdeleted TINYINT(1) NOT NULL DEFAULT 0,
  createdat DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- default settings
-- ---------------------------------------------------------------
INSERT INTO setting (settingkey, settingvalue) VALUES
('registrationfee', '100.00'),
('passvalidityyears', '2'),
('renewalyears', '1'),
('minage', '18'),
('debuglog', '1');

-- ---------------------------------------------------------------
-- master panchayat rows
-- ---------------------------------------------------------------
INSERT INTO panchayat (name, code) VALUES
('Golti-Navelim Panchayat', 'GOLTINAVELIM'),
('Malar/Sao Mathias Panchayat', 'MALARSAOMATHIAS');

-- ---------------------------------------------------------------
-- village to panchayat mapping (add more rows here if a panchayat covers additional
-- villages - match is against the Aadhaar QR's 'vtc' field, case-insensitive)
-- ---------------------------------------------------------------
INSERT INTO villagepanchayat (villagename, panchayatid) VALUES
('Golti', 1),      -- Golti-Navelim
('Navelim', 1),    -- Golti-Navelim
('Malar', 2),       -- Malar/Sao Mathias
('Sao Mathias', 2); -- Malar/Sao Mathias
