-- CWSN Portal — MySQL schema (PHP/MySQL conversion)
-- Run once against an empty database, e.g.:
--   mysql -u root -p cwsn_portal < schema.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ==================== MASTER / REFERENCE TABLES ====================

CREATE TABLE clrc (
  clrccode   VARCHAR(20)  PRIMARY KEY,
  clrcname   VARCHAR(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE block (
  blkcd      VARCHAR(20)  PRIMARY KEY,
  blkname    VARCHAR(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cluster (
  clucd      VARCHAR(20)  PRIMARY KEY,
  cluname    VARCHAR(200) NOT NULL,
  clrccode   VARCHAR(20)  NOT NULL,
  blockcode  VARCHAR(20)  NOT NULL,
  area       VARCHAR(150),
  FOREIGN KEY (clrccode) REFERENCES clrc(clrccode),
  FOREIGN KEY (blockcode) REFERENCES block(blkcd)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE schcat (
  schcat_id  VARCHAR(10)  PRIMARY KEY,
  schcat     VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE class (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  schcat_id  VARCHAR(10) NOT NULL,
  sclass     VARCHAR(20) NOT NULL,
  FOREIGN KEY (schcat_id) REFERENCES schcat(schcat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE disability (
  disability_id   VARCHAR(10)  PRIMARY KEY,
  disability_name VARCHAR(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE appliance (
  appliance_id    VARCHAR(10)  PRIMARY KEY,
  appliance_name  VARCHAR(150) NOT NULL,
  status          ENUM('0','1') NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bank (
  bank_id     VARCHAR(10)  PRIMARY KEY,
  bank_name   VARCHAR(150) NOT NULL,
  status      ENUM('0','1') NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bank_branch (
  ifsc_id      VARCHAR(40) PRIMARY KEY,   -- synthetic row id (multiple branches can share one IFSC)
  ifsc         VARCHAR(20) NOT NULL,
  bank_id      VARCHAR(10) NOT NULL,
  branch_name  VARCHAR(150) NOT NULL,
  micr         VARCHAR(20),
  FOREIGN KEY (bank_id) REFERENCES bank(bank_id),
  INDEX idx_ifsc (ifsc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE spledu (
  regno           VARCHAR(30) PRIMARY KEY,
  newregno        VARCHAR(30),
  spl_edu_name    VARCHAR(150) NOT NULL,
  clrccode        VARCHAR(20) NOT NULL,
  rehab_quali     VARCHAR(150),
  rehab_validity  VARCHAR(50),
  contact1        VARCHAR(20),
  contact2        VARCHAR(20),
  email           VARCHAR(150),
  status          ENUM('0','1') NOT NULL DEFAULT '1',
  FOREIGN KEY (clrccode) REFERENCES clrc(clrccode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE school (
  schcd      VARCHAR(20)  PRIMARY KEY,
  schname    VARCHAR(200) NOT NULL,
  clrccode   VARCHAR(20)  NOT NULL,
  schcat_id  VARCHAR(10)  NOT NULL,
  FOREIGN KEY (clrccode) REFERENCES clrc(clrccode),
  FOREIGN KEY (schcat_id) REFERENCES schcat(schcat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE awc (
  awc_no     VARCHAR(20)  PRIMARY KEY,
  awc_name   VARCHAR(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== USERS (Admin + CLRC logins) ====================

CREATE TABLE users (
  uid                  INT AUTO_INCREMENT PRIMARY KEY,
  login_id             VARCHAR(30)  NOT NULL UNIQUE,  -- 'admin' or a CLRC code
  password_hash        VARCHAR(255) NOT NULL,
  role                 ENUM('admin','clrc') NOT NULL,
  clrccode             VARCHAR(20)  NULL,
  display_name         VARCHAR(150) NOT NULL,
  active               TINYINT(1)   NOT NULL DEFAULT 1,
  must_change_password TINYINT(1)   NOT NULL DEFAULT 1,
  created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (clrccode) REFERENCES clrc(clrccode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== CWSN RECORDS ====================

CREATE TABLE cwsn (
  cwsn_code          VARCHAR(40) PRIMARY KEY,
  clrccode           VARCHAR(20) NOT NULL,
  clucd              VARCHAR(20) NOT NULL,
  cwsn_name          VARCHAR(150) NOT NULL,
  dob                DATE NULL,
  gender             ENUM('BOY','GIRL','TRANSGENDER') NULL,
  cwsn_f_name        VARCHAR(150),
  cwsn_m_name        VARCHAR(150),
  aadhaar            VARCHAR(12),
  add1               VARCHAR(100),
  add2               VARCHAR(100),
  add3               VARCHAR(60),
  pin                VARCHAR(6),
  contact_no         VARCHAR(10),
  disability_id      VARCHAR(10),
  dis_certi_yn       ENUM('YES','NO') NULL,
  dis_certi_valid_yn ENUM('YES','NO','NOT APPLICABLE') NULL,
  appliance_id       VARCHAR(10) NULL,
  therapy_reqd_yn    ENUM('YES','NO') NULL,
  therapy_provide_yn ENUM('YES','NO','NOT APPLICABLE') NULL,
  legalgurdian       VARCHAR(150),
  manabik_yn         ENUM('YES','NO') NULL,
  in_school_yn       ENUM('YES','NO') NULL,
  school_awc         ENUM('SCHOOL','AWC') NULL,
  schcd              VARCHAR(20) NULL,
  awc_no             VARCHAR(20) NULL,
  class              VARCHAR(20),
  acno               VARCHAR(30),
  ac_holder          VARCHAR(150),
  bank_id            VARCHAR(10) NULL,
  branch_name        VARCHAR(150),
  micr               VARCHAR(20),
  ifsc               VARCHAR(20),
  spledu_reg_no      VARCHAR(30) NULL,
  date_of_entry      DATE NULL,
  pen                VARCHAR(20),
  status             ENUM('0','1','2') NOT NULL DEFAULT '2',  -- 0=complete, 1=deleted, 2=incomplete draft
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (clrccode) REFERENCES clrc(clrccode),
  FOREIGN KEY (clucd) REFERENCES cluster(clucd),
  FOREIGN KEY (disability_id) REFERENCES disability(disability_id),
  FOREIGN KEY (appliance_id) REFERENCES appliance(appliance_id),
  FOREIGN KEY (bank_id) REFERENCES bank(bank_id),
  FOREIGN KEY (spledu_reg_no) REFERENCES spledu(regno),
  INDEX idx_status (status),
  INDEX idx_clrc_status (clrccode, status),
  INDEX idx_aadhaar (aadhaar),
  INDEX idx_name (cwsn_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== UDISE+ IMPORT (for the match-check feature) ====================

CREATE TABLE udise_enrollment (
  student_cd_state  VARCHAR(30) PRIMARY KEY,
  schcd             VARCHAR(20),
  student_name      VARCHAR(150),
  gender            VARCHAR(5),      -- 1=Boy, 2=Girl, 3=Transgender (UDISE+ coding)
  student_dob       DATE,
  mother_name       VARCHAR(150),
  father_name       VARCHAR(150),
  guardian_name     VARCHAR(150),
  aadhaar_no        VARCHAR(12),
  address           VARCHAR(255),
  pincode           VARCHAR(10),
  mobile_no_1       VARCHAR(15),
  mobile_no_2       VARCHAR(15),
  cwsn_yn           VARCHAR(5),
  impairment_type   VARCHAR(10),
  impairment_percent VARCHAR(10),
  pen               VARCHAR(20),
  class_id          VARCHAR(10),
  ac_year           VARCHAR(15),
  -- precomputed lookup keys, mirroring the Firestore version, so a
  -- single-record match check is a cheap indexed lookup, not a full scan
  aadhaar_norm      VARCHAR(12),
  name_dob_key      VARCHAR(300),
  INDEX idx_aadhaar_norm (aadhaar_norm),
  INDEX idx_name_dob_key (name_dob_key(191)),
  INDEX idx_schcd (schcd)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;