-- ============================================================
-- BASE DE DATOS: trabajo.otecjd.com
-- OTEC JCCD LTDA — Sistema de Gestión Integral
-- Motor: MySQL 8.0+ / MariaDB 10.5+
-- Charset: utf8mb4
-- Creado: Junio 2026
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

-- 1. USUARIOS
CREATE TABLE IF NOT EXISTS usuarios (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(50)  NOT NULL UNIQUE,
  password    VARCHAR(255) NOT NULL,
  nombre      VARCHAR(120) NOT NULL,
  rol         ENUM('superadmin','admin','ejecutor') NOT NULL DEFAULT 'ejecutor',
  activo      TINYINT(1) NOT NULL DEFAULT 1,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. TAREAS
CREATE TABLE IF NOT EXISTS tareas (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  institucion     VARCHAR(200) NOT NULL,
  n_participantes INT DEFAULT 0,
  monto_disponible DECIMAL(12,2) DEFAULT 0,
  monto_ofertado  DECIMAL(12,2) DEFAULT 0,
  fecha_cierre1   DATE NULL,
  fecha_cierre2   DATE NULL,
  asignado_a      INT NULL,
  estado          ENUM('pendiente','en_progreso','completada','cancelada') DEFAULT 'pendiente',
  prioridad       ENUM('baja','media','alta','urgente') DEFAULT 'media',
  notas           TEXT,
  created_by      INT NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (asignado_a) REFERENCES usuarios(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. PRODUCCION
CREATE TABLE IF NOT EXISTS produccion (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  nombre_programa VARCHAR(250) NOT NULL,
  fecha           DATE NULL,
  elemento        ENUM('gamma','html','genially','plan_estudio','otro') NOT NULL DEFAULT 'html',
  modulo_1        VARCHAR(200) DEFAULT '',
  modulo_2        VARCHAR(200) DEFAULT '',
  modulo_3        VARCHAR(200) DEFAULT '',
  modulo_4        VARCHAR(200) DEFAULT '',
  modulo_5        VARCHAR(200) DEFAULT '',
  modulo_6        VARCHAR(200) DEFAULT '',
  modulo_7        VARCHAR(200) DEFAULT '',
  modulo_8        VARCHAR(200) DEFAULT '',
  modulo_9        VARCHAR(200) DEFAULT '',
  modulo_10       VARCHAR(200) DEFAULT '',
  modulo_11       VARCHAR(200) DEFAULT '',
  modulo_12       VARCHAR(200) DEFAULT '',
  asignado_a      INT NULL,
  estado          ENUM('pendiente','en_progreso','completado') DEFAULT 'pendiente',
  notas           TEXT,
  created_by      INT NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (asignado_a) REFERENCES usuarios(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. COTIZACIONES
CREATE TABLE IF NOT EXISTS cotizaciones (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  razon_social    VARCHAR(200) NOT NULL,
  rut             VARCHAR(20) DEFAULT '',
  direccion       VARCHAR(250) DEFAULT '',
  giro            VARCHAR(200) DEFAULT '',
  telefono        VARCHAR(30) DEFAULT '',
  contacto        VARCHAR(150) DEFAULT '',
  estado          ENUM('por_postular','en_postulacion','cerrada','adjudicada','no_adjudicada','desierta') DEFAULT 'por_postular',
  subtotal        DECIMAL(12,2) DEFAULT 0,
  iva             DECIMAL(12,2) DEFAULT 0,
  total           DECIMAL(12,2) DEFAULT 0,
  notas           TEXT,
  created_by      INT NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. ITEMS DE COTIZACION
CREATE TABLE IF NOT EXISTS cotizacion_items (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  cotizacion_id   INT NOT NULL,
  descripcion     VARCHAR(300) NOT NULL,
  cantidad        INT DEFAULT 1,
  precio_unitario DECIMAL(12,2) DEFAULT 0,
  total           DECIMAL(12,2) DEFAULT 0,
  FOREIGN KEY (cotizacion_id) REFERENCES cotizaciones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. MERCADO PUBLICO
CREATE TABLE IF NOT EXISTS mercado_publico (
  id_postulacion  VARCHAR(50) NOT NULL PRIMARY KEY,
  institucion     VARCHAR(200) NOT NULL,
  participantes   INT DEFAULT 0,
  programa        VARCHAR(250) DEFAULT '',
  monto           DECIMAL(12,2) DEFAULT 0,
  estado          ENUM('por_postular','en_postulacion','cerrada','adjudicada','no_adjudicada','desierta') DEFAULT 'por_postular',
  notas           TEXT,
  created_by      INT NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. EJECUCION
CREATE TABLE IF NOT EXISTS ejecucion (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  oc              VARCHAR(80) DEFAULT '',
  institucion     VARCHAR(200) NOT NULL,
  fecha_inicio    DATE NULL,
  participantes   INT DEFAULT 0,
  fecha_termino   DATE NULL,
  factura         VARCHAR(80) DEFAULT '',
  fecha_facturacion DATE NULL,
  fecha_envio_informe DATE NULL,
  cobro1_monto    DECIMAL(12,2) DEFAULT 0,
  cobro1_fecha    DATE NULL,
  cobro1_estado   ENUM('pendiente','cobrado') DEFAULT 'pendiente',
  cobro2_monto    DECIMAL(12,2) DEFAULT 0,
  cobro2_fecha    DATE NULL,
  cobro2_estado   ENUM('pendiente','cobrado') DEFAULT 'pendiente',
  seguimiento     TEXT,
  estado          ENUM('activa','finalizada','cancelada') DEFAULT 'activa',
  created_by      INT NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. PLAN FINANCIERO
CREATE TABLE IF NOT EXISTS finanzas (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  tipo        ENUM('ingreso','gasto') NOT NULL,
  categoria   VARCHAR(120) DEFAULT '',
  descripcion VARCHAR(300) NOT NULL,
  monto       DECIMAL(12,2) NOT NULL,
  fecha       DATE NOT NULL,
  created_by  INT NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. MARKETING - PUBLICACIONES
CREATE TABLE IF NOT EXISTS marketing_pub (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  red_social  ENUM('facebook','instagram','tiktok','linkedin') NOT NULL,
  titulo      VARCHAR(200) NOT NULL,
  descripcion TEXT,
  fecha       DATE NOT NULL,
  alcance     INT DEFAULT 0,
  interacciones INT DEFAULT 0,
  clicks      INT DEFAULT 0,
  created_by  INT NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. MARKETING - PLANIFICACION
CREATE TABLE IF NOT EXISTS marketing_plan (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  red_social  ENUM('facebook','instagram','tiktok','linkedin') NOT NULL,
  titulo      VARCHAR(200) NOT NULL,
  descripcion TEXT,
  fecha       DATE NOT NULL,
  hora        TIME NULL,
  completado  TINYINT(1) DEFAULT 0,
  created_by  INT NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- INSERTAR USUARIOS INICIALES
INSERT INTO usuarios (username, password, nombre, rol) VALUES
('jcadiz',   'Camila17$', 'José Cádiz',      'superadmin'),
('cdelgado', 'Camila17$', 'Camila Delgado',   'admin'),
('croa',     'Camila17$', 'Cristian Roa',     'ejecutor'),
('idelgado', 'Camila17$', 'Ignacio Delgado',  'ejecutor'),
('srivera',  'Camila17$', 'Sebastián Rivera', 'ejecutor');