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

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','operator') DEFAULT 'operator',
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  father_name VARCHAR(100),
  mobile VARCHAR(20) NOT NULL,
  email VARCHAR(120),
  address TEXT,
  district VARCHAR(80),
  state VARCHAR(80),
  pincode VARCHAR(10),
  aadhaar_last4 VARCHAR(4),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status TINYINT(1) DEFAULT 1,
  INDEX(mobile)
);

CREATE TABLE services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  service_name VARCHAR(150) NOT NULL,
  category VARCHAR(100),
  customer_fee DECIMAL(10,2) DEFAULT 0,
  our_cost DECIMAL(10,2) DEFAULT 0,
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_no VARCHAR(40) NOT NULL UNIQUE,
  customer_id INT NOT NULL,
  service_id INT NOT NULL,
  amount DECIMAL(10,2) DEFAULT 0,
  status ENUM('PENDING','PROCESSING','COMPLETED','REJECTED','CANCELLED') DEFAULT 'PENDING',
  remarks TEXT,
  application_date DATE NOT NULL,
  completed_date DATE NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE RESTRICT,
  INDEX(status), INDEX(application_date)
);

CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NULL,
  customer_id INT NULL,
  type ENUM('income','expense') NOT NULL,
  category VARCHAR(100),
  amount DECIMAL(10,2) NOT NULL,
  description VARCHAR(255),
  transaction_date DATE NOT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE SET NULL,
  FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE settings (
  id INT PRIMARY KEY,
  centre_name VARCHAR(150) NOT NULL,
  owner_name VARCHAR(100),
  mobile VARCHAR(20),
  address TEXT,
  logo VARCHAR(255),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO users(name,username,password,role) VALUES
('Administrator','admin',SHA2('admin123',256),'admin');

INSERT INTO settings(id,centre_name,owner_name,mobile,address)
VALUES(1,'MY CSC CENTRE','Centre Owner','','');

INSERT INTO services(service_name,category,customer_fee,our_cost) VALUES
('PAN Card','PAN',150,107),
('Aadhaar Service','Aadhaar',50,0),
('Ayushman Card','Government',30,0),
('Voter ID','Government',50,0),
('Passport Application','Passport',200,0);