-- =====================================
-- DATABASE
-- =====================================
CREATE DATABASE IF NOT EXISTS sndcapital;
USE sndcapital;

-- =====================================
-- USERS
-- =====================================
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    mobile VARCHAR(15),
    password VARCHAR(255),
    role ENUM('user','admin') DEFAULT 'user',
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

-- =====================================
-- WALLETS
-- =====================================
CREATE TABLE wallets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    balance DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =====================================
-- MANUAL WALLET REQUESTS (UTR)
-- =====================================
CREATE TABLE wallet_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    amount DECIMAL(10,2),
    utr_number VARCHAR(50),
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =====================================
-- TRANSACTIONS
-- =====================================
CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    type ENUM('credit','debit'),
    amount DECIMAL(10,2),
    description VARCHAR(255),
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =====================================
-- SERVICES
-- =====================================
CREATE TABLE services (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_name VARCHAR(100),
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

-- =====================================
-- SERVICE SERVERS (SERVER 1 / SERVER 2)
-- =====================================
CREATE TABLE service_servers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_id BIGINT UNSIGNED,
    server_name VARCHAR(50),
    api_url TEXT,
    api_key TEXT,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (service_id) REFERENCES services(id)
);

-- =====================================
-- SERVICE CHARGES
-- =====================================
CREATE TABLE service_charges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_id BIGINT UNSIGNED,
    server_id BIGINT UNSIGNED,
    charge DECIMAL(10,2),
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (service_id) REFERENCES services(id),
    FOREIGN KEY (server_id) REFERENCES service_servers(id)
);

-- =====================================
-- DEFAULT DATA
-- =====================================

-- Admin User
INSERT INTO users (name,email,mobile,password,role,status)
VALUES ('Admin','admin@sndcapital.xyz','9999999999','123456','admin','active');

-- Sample User
INSERT INTO users (name,email,mobile,password,role,status)
VALUES ('Test User','user@sndcapital.xyz','8888888888','123456','user','active');

-- Wallets
INSERT INTO wallets (user_id,balance) VALUES (1,0),(2,0);

-- Services
INSERT INTO services (service_name,status)
VALUES ('Aadhaar To PAN Find','active');

-- Service Servers
INSERT INTO service_servers (service_id,server_name,status)
VALUES (1,'Server 1','active'),
       (1,'Server 2','active');

-- Charges
INSERT INTO service_charges (service_id,server_id,charge)
VALUES (1,1,30.00),
       (1,2,40.00);