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

-- =====================================
-- 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 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)
);

-- 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');

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