
-- HemaVishwas12 Portal Schema
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  phone VARCHAR(20) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS wallets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_wallet_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS wallet_ledger (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  type ENUM('CREDIT','DEBIT') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  reason VARCHAR(60) NOT NULL,
  reference_type VARCHAR(40) NULL,
  reference_id BIGINT NULL,
  opening_balance DECIMAL(12,2) NOT NULL,
  closing_balance DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_user (user_id),
  CONSTRAINT fk_ledger_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS services (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(60) NOT NULL UNIQUE,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  status TINYINT NOT NULL DEFAULT 1,
  endpoint_url TEXT NULL,
  http_method VARCHAR(10) NOT NULL DEFAULT 'POST',
  api_key TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  service_id BIGINT UNSIGNED NOT NULL,
  input_json TEXT NOT NULL,
  result_json MEDIUMTEXT NULL,
  cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  status ENUM('PENDING','SUCCESS','FAILED') NOT NULL DEFAULT 'PENDING',
  created_at DATETIME NOT NULL,
  INDEX idx_user (user_id),
  INDEX idx_service (service_id),
  CONSTRAINT fk_req_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_req_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  gateway VARCHAR(20) NOT NULL DEFAULT 'manual',
  order_id VARCHAR(120) NOT NULL,
  txn_id VARCHAR(120) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status ENUM('PENDING','SUCCESS','FAILED') NOT NULL DEFAULT 'PENDING',
  created_at DATETIME NOT NULL,
  INDEX idx_user (user_id),
  CONSTRAINT fk_pay_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed admin (CHANGE PASSWORD after first login)
-- Password: Admin@12345
INSERT INTO users(name,email,phone,password_hash,role,status,created_at,updated_at)
VALUES(
  'Admin',
  'admin@hemavishwas12.com',
  '9999999999',
  '$2y$10$7Pipsh1yqQbsXgB6NBpYlOeK6DEoKjsZ0xXqH3JjY8yS7q/ho.5b2',
  'admin',1,NOW(),NOW()
) ON DUPLICATE KEY UPDATE role='admin', status=1;

INSERT IGNORE INTO wallets(user_id,balance,created_at,updated_at)
SELECT id,0,NOW(),NOW() FROM users WHERE email='admin@hemavishwas12.com' LIMIT 1;

-- Example services (edit in admin panel)
INSERT IGNORE INTO services(name,code,price,status,endpoint_url,http_method,api_key,sort_order,created_at,updated_at)
VALUES
('IFSC Lookup','ifsc_lookup',5.00,1,NULL,'POST',NULL,1,NOW(),NOW()),
('Pincode Info','pincode_info',3.00,1,NULL,'POST',NULL,2,NOW(),NOW());
