CREATE DATABASE IF NOT EXISTS e_notary;
USE e_notary;

CREATE TABLE notary_applications (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  region VARCHAR(100) NOT NULL,
  district VARCHAR(100) NOT NULL,
  category ENUM('A', 'B', 'C', 'D') NOT NULL,
  registration_type ENUM('new', 'renewed') NOT NULL DEFAULT 'new',
  issued_date DATE NULL,
  expired_date DATE NULL,
  applicant_name VARCHAR(150) NULL,
  applicant_email VARCHAR(150) NULL,
  applicant_phone VARCHAR(30) NULL,
  license_file VARCHAR(255) NOT NULL,
  issued_username VARCHAR(150) NULL,
  status ENUM('pending', 'active', 'rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notaries (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  region VARCHAR(100) NOT NULL,
  district VARCHAR(100) NOT NULL,
  category ENUM('A', 'B', 'C', 'D') NOT NULL,
  registration_type ENUM('new', 'renewed') NOT NULL DEFAULT 'new',
  issued_date DATE NULL,
  expired_date DATE NULL,
  owner_user_id BIGINT NULL,
  status ENUM('pending', 'active', 'rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  notary_id BIGINT NULL,
  staff_id_code VARCHAR(50) NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  password_must_change TINYINT(1) NOT NULL DEFAULT 0,
  role ENUM(
    'super_admin',
    'ministry_justice',
    'ministry_finance',
    'owner',
    'teller',
    'payment',
    'executive'
  ) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_notary FOREIGN KEY (notary_id) REFERENCES notaries(id)
);

ALTER TABLE notaries
ADD CONSTRAINT fk_notaries_owner_user FOREIGN KEY (owner_user_id) REFERENCES users(id);

CREATE TABLE tax_rules (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  service_type VARCHAR(100) NOT NULL,
  asset_type VARCHAR(100) NOT NULL,
  min_amount DECIMAL(18, 2) NOT NULL DEFAULT 0,
  max_amount DECIMAL(18, 2) NOT NULL DEFAULT 999999999.99,
  tax_type ENUM('percentage', 'fixed') NOT NULL,
  tax_value DECIMAL(18, 2) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  notary_id BIGINT NOT NULL,
  type VARCHAR(100) NOT NULL,
  asset_type VARCHAR(100) NOT NULL,
  client_name VARCHAR(150) NOT NULL,
  amount DECIMAL(18, 2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(18, 2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(18, 2) NOT NULL DEFAULT 0,
  details JSON NULL,
  status ENUM('pending', 'paid', 'completed', 'returned') NOT NULL DEFAULT 'pending',
  created_by BIGINT NOT NULL,
  approved_by BIGINT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_transactions_notary FOREIGN KEY (notary_id) REFERENCES notaries(id),
  CONSTRAINT fk_transactions_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_transactions_approved_by FOREIGN KEY (approved_by) REFERENCES users(id),
  INDEX idx_transactions_notary_id (notary_id),
  INDEX idx_transactions_status (status),
  INDEX idx_transactions_type (type),
  INDEX idx_transactions_created_at (created_at)
);

CREATE TABLE payments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  transaction_id BIGINT NOT NULL,
  total DECIMAL(18, 2) NOT NULL,
  reference_number VARCHAR(100) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  paid_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payments_transaction FOREIGN KEY (transaction_id) REFERENCES transactions(id),
  INDEX idx_payments_transaction_id (transaction_id),
  INDEX idx_payments_paid_at (paid_at)
);

CREATE TABLE audit_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  action VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_audit_logs_user_id (user_id),
  INDEX idx_audit_logs_created_at (created_at)
);
