/* ============================================================
   CURSO DE SQL - dataconalex
   Archivo 01: Creación de la base de datos y las tablas
   Motor: Microsoft SQL Server (2016 o superior)
   ------------------------------------------------------------
   Ejecuta este archivo ANTES de 02_insertar_datos.sql
   ============================================================ */

IF DB_ID('CursoSQL') IS NULL
    CREATE DATABASE CursoSQL;
GO

USE CursoSQL;
GO

/* Se eliminan en orden inverso a las dependencias */
DROP TABLE IF EXISTS detalle_ventas;
DROP TABLE IF EXISTS ventas;
DROP TABLE IF EXISTS productos;
DROP TABLE IF EXISTS clientes;
DROP TABLE IF EXISTS empleados;
DROP TABLE IF EXISTS sucursales;
DROP TABLE IF EXISTS categorias;
GO

/* ------------------------------------------------------------
   CATEGORIAS
   Catálogo de categorías de producto.
   ------------------------------------------------------------ */
CREATE TABLE categorias (
    categoria_id      INT            NOT NULL,
    nombre_categoria  NVARCHAR(50)   NOT NULL,
    descripcion       NVARCHAR(200)  NULL,
    CONSTRAINT PK_categorias PRIMARY KEY (categoria_id)
);
GO

/* ------------------------------------------------------------
   SUCURSALES
   Tiendas físicas donde se registran las ventas.
   ------------------------------------------------------------ */
CREATE TABLE sucursales (
    sucursal_id      INT           NOT NULL,
    nombre_sucursal  NVARCHAR(60)  NOT NULL,
    ciudad           NVARCHAR(60)  NOT NULL,
    estado           NVARCHAR(60)  NOT NULL,
    fecha_apertura   DATE          NOT NULL,
    CONSTRAINT PK_sucursales PRIMARY KEY (sucursal_id)
);
GO

/* ------------------------------------------------------------
   EMPLEADOS
   jefe_id apunta a la misma tabla: sirve para SELF JOIN.
   ------------------------------------------------------------ */
CREATE TABLE empleados (
    empleado_id         INT            NOT NULL,
    nombre              NVARCHAR(50)   NOT NULL,
    apellido            NVARCHAR(50)   NOT NULL,
    puesto              NVARCHAR(40)   NOT NULL,
    jefe_id             INT            NULL,
    sucursal_id         INT            NOT NULL,
    fecha_contratacion  DATE           NOT NULL,
    salario             DECIMAL(10,2)  NOT NULL,
    activo              BIT            NOT NULL,
    CONSTRAINT PK_empleados PRIMARY KEY (empleado_id),
    CONSTRAINT FK_empleados_jefe      FOREIGN KEY (jefe_id)     REFERENCES empleados(empleado_id),
    CONSTRAINT FK_empleados_sucursal  FOREIGN KEY (sucursal_id) REFERENCES sucursales(sucursal_id)
);
GO

/* ------------------------------------------------------------
   CLIENTES
   email y telefono admiten NULL a propósito.
   ------------------------------------------------------------ */
CREATE TABLE clientes (
    cliente_id      INT           NOT NULL,
    nombre          NVARCHAR(50)  NOT NULL,
    apellido        NVARCHAR(50)  NOT NULL,
    email           NVARCHAR(100) NULL,
    telefono        NVARCHAR(20)  NULL,
    ciudad          NVARCHAR(60)  NOT NULL,
    estado          NVARCHAR(60)  NOT NULL,
    fecha_registro  DATE          NOT NULL,
    segmento        NVARCHAR(20)  NOT NULL,
    CONSTRAINT PK_clientes PRIMARY KEY (cliente_id)
);
GO

/* ------------------------------------------------------------
   PRODUCTOS
   ------------------------------------------------------------ */
CREATE TABLE productos (
    producto_id      INT            NOT NULL,
    nombre_producto  NVARCHAR(80)   NOT NULL,
    categoria_id     INT            NOT NULL,
    precio_unitario  DECIMAL(10,2)  NOT NULL,
    costo_unitario   DECIMAL(10,2)  NOT NULL,
    stock            INT            NOT NULL,
    activo           BIT            NOT NULL,
    fecha_alta       DATE           NOT NULL,
    CONSTRAINT PK_productos PRIMARY KEY (producto_id),
    CONSTRAINT FK_productos_categoria FOREIGN KEY (categoria_id) REFERENCES categorias(categoria_id)
);
GO

/* ------------------------------------------------------------
   VENTAS  (encabezado: una fila = un ticket)
   ------------------------------------------------------------ */
CREATE TABLE ventas (
    venta_id      INT           NOT NULL,
    cliente_id    INT           NOT NULL,
    empleado_id   INT           NOT NULL,
    sucursal_id   INT           NOT NULL,
    fecha_venta   DATE          NOT NULL,
    metodo_pago   NVARCHAR(30)  NOT NULL,
    estatus       NVARCHAR(20)  NOT NULL,
    CONSTRAINT PK_ventas PRIMARY KEY (venta_id),
    CONSTRAINT FK_ventas_cliente   FOREIGN KEY (cliente_id)  REFERENCES clientes(cliente_id),
    CONSTRAINT FK_ventas_empleado  FOREIGN KEY (empleado_id) REFERENCES empleados(empleado_id),
    CONSTRAINT FK_ventas_sucursal  FOREIGN KEY (sucursal_id) REFERENCES sucursales(sucursal_id)
);
GO

/* ------------------------------------------------------------
   DETALLE_VENTAS  (una fila = un producto dentro de un ticket)
   Esta tabla es la que provoca la duplicación de filas al hacer
   JOIN con ventas. Es el ejemplo del video de granularidad.
   ------------------------------------------------------------ */
CREATE TABLE detalle_ventas (
    detalle_id       INT            NOT NULL,
    venta_id         INT            NOT NULL,
    producto_id      INT            NOT NULL,
    cantidad         INT            NOT NULL,
    precio_unitario  DECIMAL(10,2)  NOT NULL,
    descuento        DECIMAL(4,2)   NULL,
    CONSTRAINT PK_detalle_ventas PRIMARY KEY (detalle_id),
    CONSTRAINT FK_detalle_venta     FOREIGN KEY (venta_id)    REFERENCES ventas(venta_id),
    CONSTRAINT FK_detalle_producto  FOREIGN KEY (producto_id) REFERENCES productos(producto_id)
);
GO

PRINT 'Tablas creadas correctamente. Ahora ejecuta 02_insertar_datos.sql';
GO
