BaseDatos.sql

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
GO

CREATE TABLE [Clientes] (
    [ClienteId] int NOT NULL IDENTITY,
    [Contrasena] nvarchar(100) NOT NULL,
    [Estado] bit NOT NULL DEFAULT CAST(1 AS bit),
    [Nombre] nvarchar(100) NOT NULL,
    [Genero] nvarchar(20) NOT NULL,
    [Edad] int NOT NULL,
    [Identificacion] nvarchar(20) NOT NULL,
    [Direccion] nvarchar(200) NOT NULL,
    [Telefono] nvarchar(20) NOT NULL,
    CONSTRAINT [PK_Clientes] PRIMARY KEY ([ClienteId])
);
GO

CREATE TABLE [Cuentas] (
    [CuentaId] int NOT NULL IDENTITY,
    [NumeroCuenta] nvarchar(20) NOT NULL,
    [TipoCuenta] nvarchar(50) NOT NULL,
    [SaldoInicial] decimal(18,2) NOT NULL,
    [Estado] bit NOT NULL DEFAULT CAST(1 AS bit),
    [ClienteId] int NOT NULL,
    CONSTRAINT [PK_Cuentas] PRIMARY KEY ([CuentaId]),
    CONSTRAINT [FK_Cuentas_Clientes_ClienteId] FOREIGN KEY ([ClienteId]) REFERENCES [Clientes] ([ClienteId]) ON DELETE NO ACTION
);
GO

CREATE TABLE [Movimientos] (
    [MovimientoId] int NOT NULL IDENTITY,
    [Fecha] datetime2 NOT NULL DEFAULT (GETDATE()),
    [TipoMovimiento] nvarchar(20) NOT NULL,
    [Valor] decimal(18,2) NOT NULL,
    [Saldo] decimal(18,2) NOT NULL,
    [Estado] bit NOT NULL DEFAULT CAST(1 AS bit),
    [CuentaId] int NOT NULL,
    CONSTRAINT [PK_Movimientos] PRIMARY KEY ([MovimientoId]),
    CONSTRAINT [FK_Movimientos_Cuentas_CuentaId] FOREIGN KEY ([CuentaId]) REFERENCES [Cuentas] ([CuentaId]) ON DELETE NO ACTION
);
GO

CREATE UNIQUE INDEX [IX_Clientes_Identificacion] ON [Clientes] ([Identificacion]);
GO

CREATE INDEX [IX_Cuentas_ClienteId] ON [Cuentas] ([ClienteId]);
GO

CREATE UNIQUE INDEX [IX_Cuentas_NumeroCuenta] ON [Cuentas] ([NumeroCuenta]);
GO

CREATE INDEX [IX_Movimientos_Cuenta_Fecha] ON [Movimientos] ([CuentaId], [Fecha]);
GO

CREATE INDEX [IX_Movimientos_Cuenta_Fecha_Tipo] ON [Movimientos] ([CuentaId], [Fecha], [TipoMovimiento]) WHERE [TipoMovimiento] = 'Debito';
GO

CREATE INDEX [IX_Movimientos_Fecha] ON [Movimientos] ([Fecha]);
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20251101022510_InitialCreate', N'8.0.11');
GO

COMMIT;
GO