CREATE DATABASE IF NOT EXISTS pawn_jewelry_shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE pawn_jewelry_shop;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS report_exports;
DROP TABLE IF EXISTS app_settings;
DROP TABLE IF EXISTS loan_timeline;
DROP TABLE IF EXISTS loan_payments;
DROP TABLE IF EXISTS jewelry_items;
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS customer_documents;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS user_refresh_tokens;
DROP TABLE IF EXISTS user_permissions;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS shops;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE shops (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_code VARCHAR(30) NOT NULL,
  shop_name VARCHAR(150) NOT NULL,
  owner_name VARCHAR(150) NULL,
  mobile VARCHAR(20) NULL,
  email VARCHAR(150) NULL,
  address TEXT NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  pincode VARCHAR(10) NULL,
  status ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_shops_shop_code (shop_code)
) ENGINE=InnoDB;

CREATE TABLE roles (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_name VARCHAR(50) NOT NULL,
  role_key VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_role_key (role_key)
) ENGINE=InnoDB;

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id BIGINT UNSIGNED NOT NULL,
  role_id TINYINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  username VARCHAR(80) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  mobile VARCHAR(20) NULL,
  email VARCHAR(150) NULL,
  status ENUM('Active', 'Disabled') NOT NULL DEFAULT 'Active',
  last_login_at DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_username (username),
  KEY idx_users_shop_id (shop_id),
  KEY idx_users_role_id (role_id),
  CONSTRAINT fk_users_shop FOREIGN KEY (shop_id) REFERENCES shops (id),
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles (id),
  CONSTRAINT fk_users_created_by FOREIGN KEY (created_by) REFERENCES users (id),
  CONSTRAINT fk_users_updated_by FOREIGN KEY (updated_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE permissions (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  permission_key VARCHAR(80) NOT NULL,
  permission_name VARCHAR(100) NOT NULL,
  module_name VARCHAR(80) NOT NULL,
  action_name ENUM('View', 'Create', 'Edit', 'Delete', 'Export', 'Manage') NOT NULL DEFAULT 'View',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_permissions_key (permission_key)
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
  role_id TINYINT UNSIGNED NOT NULL,
  permission_id SMALLINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE,
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_permissions (
  user_id BIGINT UNSIGNED NOT NULL,
  permission_id SMALLINT UNSIGNED NOT NULL,
  is_allowed TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, permission_id),
  CONSTRAINT fk_user_permissions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_user_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_refresh_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  device_name VARCHAR(150) NULL,
  ip_address VARCHAR(45) NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_refresh_tokens_user_id (user_id),
  KEY idx_refresh_tokens_expires_at (expires_at),
  CONSTRAINT fk_refresh_tokens_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id BIGINT UNSIGNED NOT NULL,
  customer_code VARCHAR(40) NOT NULL,
  customer_name VARCHAR(150) NOT NULL,
  father_or_husband_name VARCHAR(150) NULL,
  mobile VARCHAR(20) NOT NULL,
  alternative_number VARCHAR(20) NULL,
  email VARCHAR(255) NULL,
  address TEXT NOT NULL,
  date_of_birth DATE NULL,
  -- city VARCHAR(100) NOT NULL,
  -- state VARCHAR(100) NOT NULL,
  -- pincode VARCHAR(10) NOT NULL,
  id_number VARCHAR(20) NOT NULL,
  -- pan_number VARCHAR(20) NULL,
  photo_url VARCHAR(500) NULL,
  id_proof_url VARCHAR(500) NULL,
  admin_notes TEXT NULL,
  verified_customer TINYINT(1) NOT NULL DEFAULT 0,
  premium_member TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customers_shop_code (shop_id, customer_code),
  UNIQUE KEY uq_customers_shop_aadhaar (shop_id, id_number),
  KEY idx_customers_shop_mobile (shop_id, mobile),
  KEY idx_customers_shop_name (shop_id, customer_name),
  CONSTRAINT fk_customers_shop FOREIGN KEY (shop_id) REFERENCES shops (id),
  CONSTRAINT fk_customers_created_by FOREIGN KEY (created_by) REFERENCES users (id),
  CONSTRAINT fk_customers_updated_by FOREIGN KEY (updated_by) REFERENCES users (id)
) ENGINE=InnoDB;

-- CREATE TABLE customer_documents (
--   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
--   customer_id BIGINT UNSIGNED NOT NULL,
--   document_type ENUM('Photo', 'Aadhaar', 'PAN', 'Address Proof', 'Other') NOT NULL,
--   document_number VARCHAR(80) NULL,
--   file_url VARCHAR(500) NOT NULL,
--   uploaded_by BIGINT UNSIGNED NULL,
--   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
--   PRIMARY KEY (id),
--   KEY idx_customer_documents_customer_id (customer_id),
--   CONSTRAINT fk_customer_documents_customer FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE,
--   CONSTRAINT fk_customer_documents_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users (id)
-- ) ENGINE=InnoDB;

CREATE TABLE loans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  loan_number VARCHAR(40) NOT NULL,
  loan_date DATE NOT NULL,
  due_date DATE NULL,
  closed_date DATE NULL,
  loan_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  interest_percentage DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  interest_type ENUM('Monthly', 'Daily', 'Yearly') NOT NULL DEFAULT 'Monthly',
  processing_fee DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  net_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  interest_collected DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  principal_collected DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  outstanding_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  status ENUM('Active', 'Draft', 'Closed', 'Overdue', 'Renewed', 'Cancelled') NOT NULL DEFAULT 'Active',
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_loans_shop_number (shop_id, loan_number),
  KEY idx_loans_shop_status (shop_id, status),
  KEY idx_loans_customer_id (customer_id),
  KEY idx_loans_loan_date (loan_date),
  KEY idx_loans_due_date (due_date),
  CONSTRAINT fk_loans_shop FOREIGN KEY (shop_id) REFERENCES shops (id),
  CONSTRAINT fk_loans_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT fk_loans_created_by FOREIGN KEY (created_by) REFERENCES users (id),
  CONSTRAINT fk_loans_updated_by FOREIGN KEY (updated_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE jewelry_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  loan_id BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(150) NOT NULL,
  item_type VARCHAR(80) NOT NULL,
  weight DECIMAL(10,3) NOT NULL DEFAULT 0.000,
  purity VARCHAR(40) NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  estimated_value DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  remarks TEXT NULL,
  photo_url VARCHAR(500) NULL,
  status ENUM('Pledged', 'Released', 'Sold', 'Lost') NOT NULL DEFAULT 'Pledged',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_jewelry_items_loan_id (loan_id),
  KEY idx_jewelry_items_type (item_type),
  CONSTRAINT fk_jewelry_items_loan FOREIGN KEY (loan_id) REFERENCES loans (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE loan_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  loan_id BIGINT UNSIGNED NOT NULL,
  receipt_number VARCHAR(40) NOT NULL,
  payment_date DATETIME NOT NULL,
  payment_type ENUM('Interest', 'Principal', 'Part Payment', 'Closure', 'Renewal Fee', 'Other') NOT NULL,
  payment_mode ENUM('Cash', 'UPI', 'Bank Transfer', 'Card', 'Cheque', 'Other') NOT NULL DEFAULT 'Cash',
  principal_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  interest_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  fee_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  remarks TEXT NULL,
  received_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_loan_payments_receipt (receipt_number),
  KEY idx_loan_payments_loan_id (loan_id),
  KEY idx_loan_payments_payment_date (payment_date),
  CONSTRAINT fk_loan_payments_loan FOREIGN KEY (loan_id) REFERENCES loans (id) ON DELETE CASCADE,
  CONSTRAINT fk_loan_payments_received_by FOREIGN KEY (received_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE loan_timeline (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  loan_id BIGINT UNSIGNED NOT NULL,
  event_type ENUM('Created', 'Payment Received', 'Renewed', 'Closed', 'Overdue', 'Cancelled', 'PDF Generated', 'Receipt Printed') NOT NULL,
  event_title VARCHAR(150) NOT NULL,
  event_description TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_loan_timeline_loan_id (loan_id),
  CONSTRAINT fk_loan_timeline_loan FOREIGN KEY (loan_id) REFERENCES loans (id) ON DELETE CASCADE,
  CONSTRAINT fk_loan_timeline_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE app_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id BIGINT UNSIGNED NOT NULL,
  setting_key VARCHAR(100) NOT NULL,
  setting_value TEXT NULL,
  value_type ENUM('String', 'Number', 'Boolean', 'JSON') NOT NULL DEFAULT 'String',
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_app_settings_shop_key (shop_id, setting_key),
  CONSTRAINT fk_app_settings_shop FOREIGN KEY (shop_id) REFERENCES shops (id) ON DELETE CASCADE,
  CONSTRAINT fk_app_settings_updated_by FOREIGN KEY (updated_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE report_exports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id BIGINT UNSIGNED NOT NULL,
  report_type ENUM(
    'Daily Collection',
    'Interest Collection',
    'Outstanding Loans',
    'Closed Loans',
    'Loan Register',
    'Customer Register',
    'Overdue Loans',
    'Owner Summary'
  ) NOT NULL,
  export_format ENUM('PDF', 'Excel') NOT NULL,
  from_date DATE NULL,
  to_date DATE NULL,
  file_url VARCHAR(500) NULL,
  status ENUM('Queued', 'Generated', 'Failed') NOT NULL DEFAULT 'Queued',
  requested_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_report_exports_shop_type (shop_id, report_type),
  KEY idx_report_exports_created_at (created_at),
  CONSTRAINT fk_report_exports_shop FOREIGN KEY (shop_id) REFERENCES shops (id),
  CONSTRAINT fk_report_exports_requested_by FOREIGN KEY (requested_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  action_name VARCHAR(80) NOT NULL,
  old_values JSON NULL,
  new_values JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_logs_shop_entity (shop_id, entity_type, entity_id),
  KEY idx_audit_logs_user_id (user_id),
  KEY idx_audit_logs_created_at (created_at),
  CONSTRAINT fk_audit_logs_shop FOREIGN KEY (shop_id) REFERENCES shops (id),
  CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB;

INSERT INTO roles (id, role_name, role_key) VALUES
  (1, 'Owner', 'owner'),
  (2, 'Shop Keeper', 'shop_keeper');

INSERT INTO permissions (permission_key, permission_name, module_name, action_name) VALUES
  ('dashboard.view', 'View Dashboard', 'Dashboard', 'View'),
  ('customers.view', 'View Customers', 'Customers', 'View'),
  ('customers.create', 'Create Customers', 'Customers', 'Create'),
  ('customers.edit', 'Edit Customers', 'Customers', 'Edit'),
  ('customers.delete', 'Delete Customers', 'Customers', 'Delete'),
  ('loans.view', 'View Loans', 'Loans', 'View'),
  ('loans.create', 'Create Loans', 'Loans', 'Create'),
  ('loans.edit', 'Edit Loans', 'Loans', 'Edit'),
  ('loans.delete', 'Delete Loans', 'Loans', 'Delete'),
  ('loans.manage', 'Manage Loan Payments and Closure', 'Loans', 'Manage'),
  ('reports.view', 'View Reports', 'Reports', 'View'),
  ('reports.export', 'Export Reports', 'Reports', 'Export'),
  ('users.view', 'View Users', 'Users', 'View'),
  ('users.create', 'Create Users', 'Users', 'Create'),
  ('users.edit', 'Edit Users', 'Users', 'Edit'),
  ('users.delete', 'Delete Users', 'Users', 'Delete'),
  ('privileges.view', 'View User Privileges', 'Privileges', 'View'),
  ('privileges.manage', 'Manage User Privileges', 'Privileges', 'Manage'),
  ('settings.view', 'View Settings', 'Settings', 'View'),
  ('settings.manage', 'Manage Settings', 'Settings', 'Manage');

INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, id
FROM permissions
WHERE permission_key IN (
  'dashboard.view',
  'customers.view',
  'customers.create',
  'customers.edit',
  'loans.view',
  'loans.create',
  'loans.edit',
  'loans.manage',
  'reports.view',
  'reports.export'
);

INSERT INTO shops (id, shop_code, shop_name, owner_name, status) VALUES
  (1, 'MAIN', 'Pawn Jewelry Shop', 'Owner', 'Active');