CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    rol VARCHAR(20) DEFAULT 'CLIENTE',
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wallets (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    moneda VARCHAR(10) NOT NULL DEFAULT 'USDT',
    saldo_disponible DECIMAL(18, 8) DEFAULT 0.00000000,
    saldo_invertido DECIMAL(18, 8) DEFAULT 0.00000000,
    actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE investment_plans (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    duracion_dias INT NOT NULL,
    porcentaje_retorno DECIMAL(5, 2) NOT NULL,
    monto_minimo DECIMAL(18, 8) NOT NULL,
    monto_maximo DECIMAL(18, 8) NOT NULL,
    activo BOOLEAN DEFAULT TRUE
);

CREATE TABLE user_investments (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    plan_id INT REFERENCES investment_plans(id),
    monto_invertido DECIMAL(18, 8) NOT NULL,
    ganancia_estimada DECIMAL(18, 8) NOT NULL,
    fecha_inicio TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_fin TIMESTAMP NOT NULL,
    estado VARCHAR(20) DEFAULT 'ACTIVA'
);

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    tipo VARCHAR(20) NOT NULL,
    monto DECIMAL(18, 8) NOT NULL,
    moneda VARCHAR(10) DEFAULT 'USDT',
    estado VARCHAR(20) DEFAULT 'PENDIENTE',
    comprobante_url TEXT,
    datos_pago_destino TEXT,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    procesado_en TIMESTAMP,
    procesado_por_admin_id INT REFERENCES users(id)
);



-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);


-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
insert into students values (3, ' Sophie',' F');
insert into students values (4, 'Andrée',' F') ;


-- fetch some values
SELECT * FROM students WHERE gender = 'F';
select * from students where gender = 'M';
select * from students where gender = 'M' or gender = 'F';

select * from students ;
select * from transactions ;
select * from user_investments;
select * from wallets ;