-- Club Palestino CRM v2.0 - Tablas adicionales modulo Socios

CREATE TABLE IF NOT EXISTS `socios` (
    `id`              INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `empresa_id`      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `rut`             VARCHAR(12)      NOT NULL,
    `nombre_completo` VARCHAR(150)     NOT NULL,
    `tipo_socio`      ENUM('Titular Arabe','Titular Autorizado','Plan Familia') NOT NULL,
    `estado`          ENUM('activo','inactivo','suspendido','fallecido') NOT NULL DEFAULT 'activo',
    `fecha_nacimiento` DATE            NULL,
    `telefono`        VARCHAR(20)      NULL,
    `email`           VARCHAR(100)     NULL,
    `direccion`       VARCHAR(200)     NULL,
    `grupo_familiar_id` INT UNSIGNED   NULL,
    `activo`          TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`      TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_rut` (`rut`),
    KEY `idx_tipo` (`tipo_socio`,`estado`),
    KEY `idx_grupo` (`grupo_familiar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `membresias` (
    `id`          INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `socio_id`    INT UNSIGNED     NOT NULL,
    `tipo`        VARCHAR(50)      NOT NULL,
    `valor_uf`    DECIMAL(8,2)     NOT NULL,
    `valor_clp`   INT UNSIGNED     NOT NULL,
    `fecha_inicio` DATE            NOT NULL,
    `fecha_fin`   DATE             NOT NULL,
    `forma_pago`  VARCHAR(50)      NOT NULL DEFAULT 'contado',
    `activa`      TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_socio_memb` (`socio_id`,`activa`),
    KEY `idx_vencimiento` (`fecha_fin`,`activa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `socios_lockers` (
    `id`          INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `socio_id`    INT UNSIGNED     NOT NULL,
    `numero`      VARCHAR(10)      NOT NULL,
    `sector`      ENUM('hombres','mujeres') NOT NULL DEFAULT 'hombres',
    `valor_uf`    DECIMAL(6,2)     NOT NULL DEFAULT 1.00,
    `valor_clp`   INT UNSIGNED     NOT NULL DEFAULT 0,
    `fecha_inicio` DATE            NOT NULL,
    `fecha_fin`   DATE             NOT NULL,
    `activo`      TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_locker` (`numero`,`sector`,`activo`),
    KEY `idx_socio_locker` (`socio_id`,`activo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pagos_socios` (
    `id`          INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `socio_id`    INT UNSIGNED     NOT NULL,
    `concepto`    VARCHAR(150)     NOT NULL,
    `monto_uf`    DECIMAL(8,2)     NOT NULL DEFAULT 0,
    `monto_clp`   INT UNSIGNED     NOT NULL DEFAULT 0,
    `forma_pago`  VARCHAR(50)      NOT NULL DEFAULT 'contado',
    `fecha`       DATE             NOT NULL,
    `created_at`  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_socio_pago` (`socio_id`,`fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `usuarios` (
    `id`          INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `empresa_id`  TINYINT UNSIGNED NULL,
    `nombre`      VARCHAR(150)     NOT NULL,
    `email`       VARCHAR(100)     NOT NULL,
    `rut`         VARCHAR(12)      NULL,
    `password_hash` VARCHAR(255)   NOT NULL,
    `tipo`        ENUM('admin','contador','rrhh','cajero','recepcion') NOT NULL DEFAULT 'recepcion',
    `activo`      TINYINT(1)       NOT NULL DEFAULT 1,
    `ultimo_login` TIMESTAMP       NULL,
    `created_at`  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;