-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `albarataicommx_marketplace`
--

-- --------------------------------------------------------

--
-- Table structure for table `admin_logs`
--

CREATE TABLE `admin_logs` (
  `id` int UNSIGNED NOT NULL,
  `admin_id` int UNSIGNED NOT NULL,
  `action` varchar(100) NOT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` int UNSIGNED DEFAULT NULL,
  `notes` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `admin_logs`
--

INSERT INTO `admin_logs` (`id`, `admin_id`, `action`, `entity_type`, `entity_id`, `notes`, `created_at`) VALUES
(1, 1, 'enable_category', 'categories', 1, NULL, '2026-05-11 06:27:12'),
(2, 1, 'enable_category', 'categories', 2, NULL, '2026-05-11 06:27:16'),
(3, 1, 'enable_category', 'categories', 3, NULL, '2026-05-11 06:27:19'),
(4, 1, 'enable_category', 'categories', 4, NULL, '2026-05-11 06:27:26'),
(5, 1, 'enable_category', 'categories', 5, NULL, '2026-05-11 06:27:29'),
(6, 1, 'enable_category', 'categories', 6, NULL, '2026-05-11 06:27:33'),
(7, 1, 'enable_category', 'categories', 12, NULL, '2026-05-11 07:11:27'),
(8, 1, 'enable_category', 'categories', 13, NULL, '2026-05-11 07:11:32');

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` int UNSIGNED NOT NULL,
  `parent_id` int UNSIGNED DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `type` enum('service','product','both') DEFAULT 'both',
  `icon` varchar(100) DEFAULT NULL,
  `sort_order` int DEFAULT '0',
  `status` tinyint(1) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `type`, `icon`, `sort_order`, `status`) VALUES
(1, NULL, 'IT y Tecnología', 'it-tecnologia', 'service', '?', 1, 1),
(2, NULL, 'Hogar y Reparaciones', 'hogar', 'service', '?', 2, 1),
(3, NULL, 'Educación y Clases', 'educacion', 'service', '?', 3, 1),
(4, NULL, 'Diseño y Creatividad', 'diseno', 'service', '?', 4, 1),
(5, NULL, 'Marketing y Ventas', 'marketing', 'service', '?', 5, 1),
(6, NULL, 'Legal y Financiero', 'legal', 'service', '⚖️', 6, 1),
(7, NULL, 'Salud y Bienestar', 'salud', 'both', '❤️', 7, 1),
(8, NULL, 'Eventos', 'eventos', 'service', '?', 8, 1),
(9, NULL, 'Autos y Transporte', 'autos', 'both', '?', 9, 1),
(10, NULL, 'Productos Electrónicos', 'electronicos', 'product', '?', 10, 1),
(11, NULL, 'Ropa y Accesorios', 'ropa', 'product', '?', 11, 1),
(12, NULL, 'Hogar (Productos)', 'productos-hogar', 'product', '?️', 12, 1),
(13, NULL, 'Deportes y Hobbies', 'deportes', 'product', '⚽', 13, 1),
(101, 1, 'Diseño Gráfico', 'diseno-grafico', 'service', NULL, 1, 1),
(102, 1, 'Desarrollo Web', 'desarrollo-web', 'service', NULL, 2, 1),
(103, 1, 'Apps Móviles', 'apps-moviles', 'service', NULL, 3, 1),
(104, 1, 'Marketing Digital', 'marketing-digital', 'service', NULL, 4, 1),
(105, 1, 'Soporte Técnico', 'soporte-tecnico', 'service', NULL, 5, 1),
(106, 1, 'Bases de Datos', 'bases-de-datos', 'service', NULL, 6, 1),
(107, 1, 'Inteligencia Artificial', 'ia', 'service', NULL, 7, 1),
(201, 2, 'Plomería', 'plomeria', 'service', NULL, 1, 1),
(202, 2, 'Electricidad', 'electricidad', 'service', NULL, 2, 1),
(203, 2, 'Pintura', 'pintura', 'service', NULL, 3, 1),
(204, 2, 'Carpintería', 'carpinteria', 'service', NULL, 4, 1),
(205, 2, 'Albañilería', 'albanileria', 'service', NULL, 5, 1),
(206, 2, 'Jardinería', 'jardineria', 'service', NULL, 6, 1),
(207, 2, 'Limpieza del Hogar', 'limpieza', 'service', NULL, 7, 1),
(208, 2, 'Aire Acondicionado', 'aire-acondicionado', 'service', NULL, 8, 1),
(209, 2, 'Mudanzas y Fletes', 'mudanzas', 'service', NULL, 9, 1),
(210, 2, 'Herrería', 'herreria', 'service', NULL, 10, 1),
(301, 3, 'Idiomas', 'idiomas', 'service', NULL, 1, 1),
(302, 3, 'Matemáticas y Ciencias', 'matematicas', 'service', NULL, 2, 1),
(303, 3, 'Música', 'musica-clases', 'service', NULL, 3, 1),
(304, 3, 'Tutorías Escolares', 'tutorias', 'service', NULL, 4, 1),
(305, 3, 'Programación', 'programacion-clases', 'service', NULL, 5, 1),
(306, 3, 'Arte y Manualidades', 'arte-clases', 'service', NULL, 6, 1),
(401, 4, 'Fotografía', 'fotografia', 'service', NULL, 1, 1),
(402, 4, 'Video y Edición', 'video-edicion', 'service', NULL, 2, 1),
(403, 4, 'Música y Audio', 'musica-audio', 'service', NULL, 3, 1),
(404, 4, 'Moda y Estilismo', 'moda', 'service', NULL, 4, 1),
(405, 4, 'Escritura y Redacción', 'escritura', 'service', NULL, 5, 1),
(501, 5, 'Community Manager', 'community-manager', 'service', NULL, 1, 1),
(502, 5, 'SEO y Posicionamiento', 'seo', 'service', NULL, 2, 1),
(503, 5, 'Publicidad Online', 'publicidad-online', 'service', NULL, 3, 1),
(504, 5, 'Copywriting', 'copywriting', 'service', NULL, 4, 1),
(505, 5, 'Email Marketing', 'email-marketing', 'service', NULL, 5, 1),
(506, 5, 'Ventas y Telemarketing', 'ventas', 'service', NULL, 6, 1),
(601, 6, 'Abogados', 'abogados', 'service', NULL, 1, 1),
(602, 6, 'Contabilidad', 'contabilidad', 'service', NULL, 2, 1),
(603, 6, 'Recursos Humanos', 'rh', 'service', NULL, 3, 1),
(604, 6, 'Trámites y Gestiones', 'tramites', 'service', NULL, 4, 1),
(701, 7, 'Nutrición', 'nutricion', 'service', NULL, 1, 1),
(702, 7, 'Entrenamiento Personal', 'entrenamiento', 'service', NULL, 2, 1),
(703, 7, 'Psicología', 'psicologia', 'service', NULL, 3, 1),
(704, 7, 'Masajes y Terapias', 'masajes', 'service', NULL, 4, 1),
(705, 7, 'Médicos a Domicilio', 'medicos', 'service', NULL, 5, 1),
(706, 7, 'Veterinaria', 'veterinaria', 'service', NULL, 6, 1),
(707, 7, 'Productos de Salud', 'productos-salud', 'product', NULL, 7, 1),
(801, 8, 'Organización de Eventos', 'org-eventos', 'service', NULL, 1, 1),
(802, 8, 'DJ y Música en Vivo', 'dj-musica', 'service', NULL, 2, 1),
(803, 8, 'Catering y Comida', 'catering', 'service', NULL, 3, 1),
(804, 8, 'Decoración', 'decoracion-eventos', 'service', NULL, 4, 1),
(805, 8, 'Animación Infantil', 'animacion-infantil', 'service', NULL, 5, 1),
(806, 8, 'Renta de Equipo', 'renta-equipo', 'both', NULL, 6, 1),
(901, 9, 'Mecánica Automotriz', 'mecanica', 'service', NULL, 1, 1),
(902, 9, 'Lavado y Detallado', 'lavado-autos', 'service', NULL, 2, 1),
(903, 9, 'Choferes y Traslados', 'choferes', 'service', NULL, 3, 1),
(904, 9, 'Refacciones y Partes', 'refacciones', 'product', NULL, 4, 1),
(905, 9, 'Grúas y Auxilio Vial', 'gruas', 'service', NULL, 5, 1),
(1001, 10, 'Celulares y Tablets', 'celulares', 'product', NULL, 1, 1),
(1002, 10, 'Computadoras', 'computadoras', 'product', NULL, 2, 1),
(1003, 10, 'Accesorios Tech', 'accesorios-tech', 'product', NULL, 3, 1),
(1004, 10, 'Consolas y Videojuegos', 'consolas', 'product', NULL, 4, 1),
(1005, 10, 'Audio y TV', 'audio-tv', 'product', NULL, 5, 1),
(1101, 11, 'Ropa de Hombre', 'ropa-hombre', 'product', NULL, 1, 1),
(1102, 11, 'Ropa de Mujer', 'ropa-mujer', 'product', NULL, 2, 1),
(1103, 11, 'Ropa de Niños', 'ropa-ninos', 'product', NULL, 3, 1),
(1104, 11, 'Calzado', 'calzado', 'product', NULL, 4, 1),
(1105, 11, 'Bolsas y Accesorios', 'bolsas', 'product', NULL, 5, 1),
(1201, 12, 'Muebles', 'muebles', 'product', NULL, 1, 1),
(1202, 12, 'Electrodomésticos', 'electrodomesticos', 'product', NULL, 2, 1),
(1203, 12, 'Decoración del Hogar', 'decoracion-hogar', 'product', NULL, 3, 1),
(1204, 12, 'Herramientas', 'herramientas', 'product', NULL, 4, 1),
(1205, 12, 'Cocina y Comedor', 'cocina', 'product', NULL, 5, 1),
(1301, 13, 'Bicicletas', 'bicicletas', 'product', NULL, 1, 1),
(1302, 13, 'Equipo de Gimnasio', 'gimnasio', 'product', NULL, 2, 1),
(1303, 13, 'Deportes de Equipo', 'deportes-equipo', 'product', NULL, 3, 1),
(1304, 13, 'Coleccionables', 'coleccionables', 'product', NULL, 4, 1),
(1305, 13, 'Camping y Outdoor', 'camping', 'product', NULL, 5, 1),
(1306, 101, 'Logo y Branding', 'logo-branding', 'service', NULL, 1, 1),
(1307, 101, 'Diseño de Banners', 'diseno-banners', 'service', NULL, 2, 1),
(1308, 101, 'UI/UX Design', 'ui-ux', 'service', NULL, 3, 1),
(1309, 101, 'Ilustración', 'ilustracion', 'service', NULL, 4, 1),
(1310, 101, 'Infografías', 'infografias', 'service', NULL, 5, 1),
(1311, 101, 'Diseño de Presentaciones', 'diseno-presentaciones', 'service', NULL, 6, 1),
(1312, 102, 'Landing Pages', 'landing-pages', 'service', NULL, 1, 1),
(1313, 102, 'E-commerce', 'ecommerce', 'service', NULL, 2, 1),
(1314, 102, 'WordPress', 'wordpress', 'service', NULL, 3, 1),
(1315, 102, 'Desarrollo a Medida', 'dev-a-medida', 'service', NULL, 4, 1),
(1316, 102, 'APIs y Backend', 'apis-backend', 'service', NULL, 5, 1),
(1317, 102, 'Mantenimiento Web', 'mantenimiento-web', 'service', NULL, 6, 1),
(1318, 103, 'Android', 'android', 'service', NULL, 1, 1),
(1319, 103, 'iOS', 'ios', 'service', NULL, 2, 1),
(1320, 103, 'React Native / Flutter', 'react-native', 'service', NULL, 3, 1),
(1321, 103, 'PWA', 'pwa', 'service', NULL, 4, 1),
(1322, 104, 'Facebook e Instagram Ads', 'fb-ig-ads', 'service', NULL, 1, 1),
(1323, 104, 'Google Ads', 'google-ads', 'service', NULL, 2, 1),
(1324, 104, 'TikTok Ads', 'tiktok-ads', 'service', NULL, 3, 1),
(1325, 104, 'Estrategia Digital', 'estrategia-digital', 'service', NULL, 4, 1),
(1326, 105, 'Reparación de PC / Laptop', 'reparacion-pc', 'service', NULL, 1, 1),
(1327, 105, 'Redes e Internet', 'redes', 'service', NULL, 2, 1),
(1328, 105, 'Instalación de Software', 'software-install', 'service', NULL, 3, 1),
(1329, 105, 'Seguridad Informática', 'seguridad-info', 'service', NULL, 4, 1),
(1330, 301, 'Inglés', 'ingles', 'service', NULL, 1, 1),
(1331, 301, 'Francés', 'frances', 'service', NULL, 2, 1),
(1332, 301, 'Alemán', 'aleman', 'service', NULL, 3, 1),
(1333, 301, 'Portugués', 'portugues', 'service', NULL, 4, 1),
(1334, 301, 'Mandarín', 'mandarin', 'service', NULL, 5, 1),
(1335, 303, 'Guitarra', 'guitarra', 'service', NULL, 1, 1),
(1336, 303, 'Piano', 'piano', 'service', NULL, 2, 1),
(1337, 303, 'Canto', 'canto', 'service', NULL, 3, 1),
(1338, 303, 'Batería', 'bateria', 'service', NULL, 4, 1),
(1339, 401, 'Fotografía de Eventos', 'foto-eventos', 'service', NULL, 1, 1),
(1340, 401, 'Fotografía de Productos', 'foto-productos', 'service', NULL, 2, 1),
(1341, 401, 'Fotografía de Retratos', 'foto-retratos', 'service', NULL, 3, 1),
(1342, 401, 'Fotografía Inmobiliaria', 'foto-inmobiliaria', 'service', NULL, 4, 1),
(1343, 402, 'Edición de Video', 'edicion-video', 'service', NULL, 1, 1),
(1344, 402, 'Animación 2D / 3D', 'animacion', 'service', NULL, 2, 1),
(1345, 402, 'Reels e Instagram', 'reels', 'service', NULL, 3, 1),
(1346, 402, 'YouTube y Streaming', 'youtube', 'service', NULL, 4, 1),
(1347, 601, 'Derecho Familiar', 'derecho-familiar', 'service', NULL, 1, 1),
(1348, 601, 'Derecho Laboral', 'derecho-laboral', 'service', NULL, 2, 1),
(1349, 601, 'Derecho Mercantil', 'derecho-mercantil', 'service', NULL, 3, 1),
(1350, 601, 'Derecho Inmobiliario', 'derecho-inmobiliario', 'service', NULL, 4, 1),
(1351, 602, 'Declaraciones de Impuestos', 'declaraciones', 'service', NULL, 1, 1),
(1352, 602, 'Facturación CFDI', 'cfdi', 'service', NULL, 2, 1),
(1353, 602, 'Nóminas', 'nominas', 'service', NULL, 3, 1),
(1354, 602, 'Auditoría', 'auditoria', 'service', NULL, 4, 1),
(1355, 402, 'xxxx', 'xxxx', 'both', '', 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `deals`
--

CREATE TABLE `deals` (
  `id` int UNSIGNED NOT NULL,
  `requirement_id` int UNSIGNED NOT NULL,
  `offer_id` int UNSIGNED NOT NULL,
  `buyer_id` int UNSIGNED NOT NULL,
  `seller_id` int UNSIGNED NOT NULL,
  `agreed_price` decimal(10,2) DEFAULT NULL,
  `delivery_days` int DEFAULT NULL,
  `status` enum('active','completed','cancelled','disputed') DEFAULT 'active',
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `deals`
--

INSERT INTO `deals` (`id`, `requirement_id`, `offer_id`, `buyer_id`, `seller_id`, `agreed_price`, `delivery_days`, `status`, `completed_at`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 2, 3, 90.00, 2, 'active', NULL, '2026-05-11 09:26:34', '2026-05-11 09:26:34');

-- --------------------------------------------------------

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` int UNSIGNED NOT NULL,
  `sender_id` int UNSIGNED NOT NULL,
  `receiver_id` int UNSIGNED NOT NULL,
  `requirement_id` int UNSIGNED DEFAULT NULL,
  `deal_id` int UNSIGNED DEFAULT NULL,
  `body` text NOT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `messages`
--

INSERT INTO `messages` (`id`, `sender_id`, `receiver_id`, `requirement_id`, `deal_id`, `body`, `is_read`, `created_at`) VALUES
(1, 3, 2, NULL, 1, 'HI', 1, '2026-05-11 09:36:42'),
(2, 3, 2, NULL, 1, 'let\'s have a final dicussion', 1, '2026-05-11 09:38:51'),
(3, 2, 3, NULL, 1, 'ok', 1, '2026-05-11 09:53:34'),
(4, 3, 2, NULL, 1, 'Hi Atul, I was busy', 1, '2026-05-11 11:04:25');

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `type` varchar(60) NOT NULL,
  `title` varchar(150) DEFAULT NULL,
  `body` text,
  `link` varchar(255) DEFAULT NULL,
  `data` json DEFAULT NULL,
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `user_id`, `type`, `title`, `body`, `link`, `data`, `read_at`, `created_at`) VALUES
(1, 2, 'message', 'Nuevo mensaje de Sumit', 'Hi Atul, I was busy', '/mensajes', NULL, '2026-05-11 11:04:43', '2026-05-11 11:04:25');

-- --------------------------------------------------------

--
-- Table structure for table `offers`
--

CREATE TABLE `offers` (
  `id` int UNSIGNED NOT NULL,
  `requirement_id` int UNSIGNED NOT NULL,
  `seller_id` int UNSIGNED NOT NULL,
  `cover_letter` text NOT NULL,
  `what_includes` text,
  `price_type` enum('fixed','hourly','quote') DEFAULT 'fixed',
  `price` decimal(10,2) DEFAULT NULL,
  `delivery_days` int DEFAULT NULL,
  `availability` varchar(100) DEFAULT NULL,
  `guarantee_days` int DEFAULT '0',
  `portfolio_url` varchar(255) DEFAULT NULL,
  `status` enum('pending','accepted','rejected','withdrawn') DEFAULT 'pending',
  `seen_by_buyer` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `offers`
--

INSERT INTO `offers` (`id`, `requirement_id`, `seller_id`, `cover_letter`, `what_includes`, `price_type`, `price`, `delivery_days`, `availability`, `guarantee_days`, `portfolio_url`, `status`, `seen_by_buyer`, `created_at`, `updated_at`) VALUES
(1, 1, 3, 'Hola, tengo experiencia trabajando en proyectos similares y puedo ayudarte a desarrollar una solución profesional, moderna y funcional según tus necesidades.\n\nMe especializo en desarrollo web, diseño de interfaces y creación de plataformas dinámicas. Mi objetivo es entregar un trabajo de alta calidad, con buena comunicación y cumplimiento de tiempos.\n\n¿Qué incluye mi propuesta?\n• Desarrollo profesional y responsive\n• Diseño moderno y fácil de usar\n• Optimización y buen rendimiento\n• Código limpio y escalable\n• Soporte durante el proceso del proyecto\n\nAdemás, mantendré una comunicación constante para asegurar que el resultado final sea exactamente lo que buscas. Estoy listo para comenzar inmediatamente y aportar ideas para mejorar tu proyecto.\n\nSerá un placer trabajar contigo.', NULL, 'fixed', 90.00, 2, NULL, 0, NULL, 'accepted', 0, '2026-05-11 09:23:31', '2026-05-11 09:26:34');

-- --------------------------------------------------------

--
-- Table structure for table `offer_attachments`
--

CREATE TABLE `offer_attachments` (
  `id` int UNSIGNED NOT NULL,
  `offer_id` int UNSIGNED NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(50) DEFAULT NULL,
  `file_size` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `offer_qa`
--

CREATE TABLE `offer_qa` (
  `id` int UNSIGNED NOT NULL,
  `offer_id` int UNSIGNED NOT NULL,
  `question` varchar(255) NOT NULL,
  `answer` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `plans`
--

CREATE TABLE `plans` (
  `id` int UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(10,2) DEFAULT '0.00',
  `duration_days` int DEFAULT '30',
  `proposals_per_day` int DEFAULT '5',
  `messages_per_day` int DEFAULT '10',
  `featured_profile` tinyint(1) DEFAULT '0',
  `priority_listing` tinyint(1) DEFAULT '0',
  `badge` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `plans`
--

INSERT INTO `plans` (`id`, `name`, `price`, `duration_days`, `proposals_per_day`, `messages_per_day`, `featured_profile`, `priority_listing`, `badge`, `is_active`, `sort_order`, `created_at`) VALUES
(1, 'Gratis', 0.00, 0, 5, 10, 0, 0, NULL, 1, 1, '2026-05-06 05:05:12'),
(2, 'Básico', 299.00, 30, 15, 50, 0, 0, NULL, 1, 2, '2026-05-06 05:05:12'),
(3, 'Pro', 599.00, 30, 40, 150, 1, 1, 'Pro Seller', 1, 3, '2026-05-06 05:05:12'),
(4, 'Premium', 999.00, 30, 999, 999, 1, 1, 'Top Seller', 1, 4, '2026-05-06 05:05:12');

-- --------------------------------------------------------

--
-- Table structure for table `requirements`
--

CREATE TABLE `requirements` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `category_id` int UNSIGNED DEFAULT NULL,
  `title` varchar(80) NOT NULL,
  `description` text NOT NULL,
  `type` enum('service','product') NOT NULL,
  `budget_min` decimal(10,2) DEFAULT NULL,
  `budget_max` decimal(10,2) DEFAULT NULL,
  `modality` enum('presencial','remoto','ambas') DEFAULT 'ambas',
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `deadline` date DEFAULT NULL,
  `max_proposals` int DEFAULT '10',
  `is_urgent` tinyint(1) DEFAULT '0',
  `status` enum('open','in_progress','completed','cancelled','closed') DEFAULT 'open',
  `views` int DEFAULT '0',
  `keywords` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `requirements`
--

INSERT INTO `requirements` (`id`, `user_id`, `category_id`, `title`, `description`, `type`, `budget_min`, `budget_max`, `modality`, `city`, `state`, `deadline`, `max_proposals`, `is_urgent`, `status`, `views`, `keywords`, `created_at`, `updated_at`) VALUES
(1, 2, 101, 'Necesito un diseño profesional de brochure y presentación para un producto SaaS', 'Estamos buscando un diseñador gráfico creativo para diseñar un brochure y una presentación profesional para nuestro producto SaaS llamado “ApplyTech”.\n\nApplyTech es una plataforma de software diseñada para consultorías de educación internacional. El diseño debe explicar claramente:\n\nQué es ApplyTech\nLos diferentes modelos de negocio de las consultorías\nLos módulos disponibles (Administrador, Estudiante, Agente y Universidad)\nCómo funciona el sistema SaaS con dominios y subdominios\nSus características y beneficios', 'service', NULL, 500.00, '', NULL, '', '2026-05-15', 10, 0, 'in_progress', 13, 'SaaS, Diseño Gráfico, Presentación Corporativa, Brochure Profesional', '2026-05-11 07:24:22', '2026-05-11 11:31:58'),
(2, 3, 101, 'Define tu presupuesto. Ayuda a filtrar proveedores que se ajusten a tu bolsillo.', 'Define tu presupuesto. Ayuda a filtrar proveedores que se ajusten a tu bolsillo.Define tu presupuesto. Ayuda a filtrar proveedores que se ajusten a tu bolsillo.Define tu presupuesto. Ayuda a filtrar proveedores que se ajusten a tu bolsillo.', 'service', NULL, 500.00, '', NULL, 'Mexico', '0000-00-00', 10, 0, 'open', 6, '', '2026-05-11 10:57:47', '2026-05-11 11:32:51');

-- --------------------------------------------------------

--
-- Table structure for table `requirement_attachments`
--

CREATE TABLE `requirement_attachments` (
  `id` int UNSIGNED NOT NULL,
  `requirement_id` int UNSIGNED NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(50) DEFAULT NULL,
  `file_size` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `reviews`
--

CREATE TABLE `reviews` (
  `id` int UNSIGNED NOT NULL,
  `deal_id` int UNSIGNED NOT NULL,
  `reviewer_id` int UNSIGNED NOT NULL,
  `reviewee_id` int UNSIGNED NOT NULL,
  `rating` tinyint NOT NULL,
  `comment` text,
  `type` enum('buyer_to_seller','seller_to_buyer') NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ;

-- --------------------------------------------------------

--
-- Table structure for table `seller_categories`
--

CREATE TABLE `seller_categories` (
  `id` int UNSIGNED NOT NULL,
  `seller_id` int UNSIGNED NOT NULL,
  `category_id` int UNSIGNED NOT NULL,
  `offer_type` enum('service','product') DEFAULT 'service'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `seller_daily_usage`
--

CREATE TABLE `seller_daily_usage` (
  `id` int UNSIGNED NOT NULL,
  `seller_id` int UNSIGNED NOT NULL,
  `usage_date` date NOT NULL,
  `proposals_sent` int DEFAULT '0',
  `messages_sent` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `seller_daily_usage`
--

INSERT INTO `seller_daily_usage` (`id`, `seller_id`, `usage_date`, `proposals_sent`, `messages_sent`) VALUES
(1, 3, '2026-05-11', 1, 3),
(4, 2, '2026-05-11', 0, 1);

-- --------------------------------------------------------

--
-- Table structure for table `seller_locations`
--

CREATE TABLE `seller_locations` (
  `id` int UNSIGNED NOT NULL,
  `seller_id` int UNSIGNED NOT NULL,
  `state` varchar(100) NOT NULL,
  `city` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `seller_profiles`
--

CREATE TABLE `seller_profiles` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `business_name` varchar(150) DEFAULT NULL,
  `business_type` enum('individual','company') DEFAULT 'individual',
  `tagline` varchar(150) DEFAULT NULL,
  `description` text,
  `offer_type` enum('service','product','both') DEFAULT 'service',
  `whatsapp` varchar(20) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `portfolio_url` varchar(255) DEFAULT NULL,
  `avg_rating` decimal(2,1) DEFAULT '0.0',
  `total_reviews` int DEFAULT '0',
  `total_deals` int DEFAULT '0',
  `is_verified` tinyint(1) DEFAULT '0',
  `is_featured` tinyint(1) DEFAULT '0',
  `profile_completion` tinyint DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `seller_skills`
--

CREATE TABLE `seller_skills` (
  `id` int UNSIGNED NOT NULL,
  `seller_id` int UNSIGNED NOT NULL,
  `skill` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `seller_subscriptions`
--

CREATE TABLE `seller_subscriptions` (
  `id` int UNSIGNED NOT NULL,
  `seller_id` int UNSIGNED NOT NULL,
  `plan_id` int UNSIGNED NOT NULL,
  `starts_at` datetime NOT NULL,
  `expires_at` datetime NOT NULL,
  `status` enum('active','expired','cancelled') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admin_logs`
--
ALTER TABLE `admin_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `admin_id` (`admin_id`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `parent_id` (`parent_id`);

--
-- Indexes for table `deals`
--
ALTER TABLE `deals`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `offer_id` (`offer_id`),
  ADD KEY `requirement_id` (`requirement_id`),
  ADD KEY `buyer_id` (`buyer_id`),
  ADD KEY `seller_id` (`seller_id`);

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sender_id` (`sender_id`),
  ADD KEY `receiver_id` (`receiver_id`),
  ADD KEY `requirement_id` (`requirement_id`),
  ADD KEY `deal_id` (`deal_id`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `offers`
--
ALTER TABLE `offers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `one_offer_per_req` (`requirement_id`,`seller_id`),
  ADD KEY `seller_id` (`seller_id`);

--
-- Indexes for table `offer_attachments`
--
ALTER TABLE `offer_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `offer_id` (`offer_id`);

--
-- Indexes for table `offer_qa`
--
ALTER TABLE `offer_qa`
  ADD PRIMARY KEY (`id`),
  ADD KEY `offer_id` (`offer_id`);

--
-- Indexes for table `plans`
--
ALTER TABLE `plans`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `requirements`
--
ALTER TABLE `requirements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `category_id` (`category_id`);

--
-- Indexes for table `requirement_attachments`
--
ALTER TABLE `requirement_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `requirement_id` (`requirement_id`);

--
-- Indexes for table `reviews`
--
ALTER TABLE `reviews`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `one_review_per_side` (`deal_id`,`type`),
  ADD KEY `reviewer_id` (`reviewer_id`),
  ADD KEY `reviewee_id` (`reviewee_id`);

--
-- Indexes for table `seller_categories`
--
ALTER TABLE `seller_categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_seller_category` (`seller_id`,`category_id`),
  ADD KEY `category_id` (`category_id`);

--
-- Indexes for table `seller_daily_usage`
--
ALTER TABLE `seller_daily_usage`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_seller_date` (`seller_id`,`usage_date`);

--
-- Indexes for table `seller_locations`
--
ALTER TABLE `seller_locations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `seller_id` (`seller_id`);

--
-- Indexes for table `seller_profiles`
--
ALTER TABLE `seller_profiles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`);

--
-- Indexes for table `seller_skills`
--
ALTER TABLE `seller_skills`
  ADD PRIMARY KEY (`id`),
  ADD KEY `seller_id` (`seller_id`);

--
-- Indexes for table `seller_subscriptions`
--
ALTER TABLE `seller_subscriptions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `seller_id` (`seller_id`),
  ADD KEY `plan_id` (`plan_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admin_logs`
--
ALTER TABLE `admin_logs`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1356;

--
-- AUTO_INCREMENT for table `deals`
--
ALTER TABLE `deals`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `offers`
--
ALTER TABLE `offers`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `offer_attachments`
--
ALTER TABLE `offer_attachments`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `offer_qa`
--
ALTER TABLE `offer_qa`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `plans`
--
ALTER TABLE `plans`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `requirements`
--
ALTER TABLE `requirements`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `requirement_attachments`
--
ALTER TABLE `requirement_attachments`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `reviews`
--
ALTER TABLE `reviews`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `seller_categories`
--
ALTER TABLE `seller_categories`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `seller_daily_usage`
--
ALTER TABLE `seller_daily_usage`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `seller_locations`
--
ALTER TABLE `seller_locations`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `seller_profiles`
--
ALTER TABLE `seller_profiles`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `seller_skills`
--
ALTER TABLE `seller_skills`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `seller_subscriptions`
--
ALTER TABLE `seller_subscriptions`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `admin_logs`
--
ALTER TABLE `admin_logs`
  ADD CONSTRAINT `admin_logs_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `categories`
--
ALTER TABLE `categories`
  ADD CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `deals`
--
ALTER TABLE `deals`
  ADD CONSTRAINT `deals_ibfk_1` FOREIGN KEY (`requirement_id`) REFERENCES `requirements` (`id`),
  ADD CONSTRAINT `deals_ibfk_2` FOREIGN KEY (`offer_id`) REFERENCES `offers` (`id`),
  ADD CONSTRAINT `deals_ibfk_3` FOREIGN KEY (`buyer_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `deals_ibfk_4` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
  ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `messages_ibfk_3` FOREIGN KEY (`requirement_id`) REFERENCES `requirements` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `messages_ibfk_4` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `offers`
--
ALTER TABLE `offers`
  ADD CONSTRAINT `offers_ibfk_1` FOREIGN KEY (`requirement_id`) REFERENCES `requirements` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `offers_ibfk_2` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `offer_attachments`
--
ALTER TABLE `offer_attachments`
  ADD CONSTRAINT `offer_attachments_ibfk_1` FOREIGN KEY (`offer_id`) REFERENCES `offers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `offer_qa`
--
ALTER TABLE `offer_qa`
  ADD CONSTRAINT `offer_qa_ibfk_1` FOREIGN KEY (`offer_id`) REFERENCES `offers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `requirements`
--
ALTER TABLE `requirements`
  ADD CONSTRAINT `requirements_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `requirements_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `requirement_attachments`
--
ALTER TABLE `requirement_attachments`
  ADD CONSTRAINT `requirement_attachments_ibfk_1` FOREIGN KEY (`requirement_id`) REFERENCES `requirements` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `reviews`
--
ALTER TABLE `reviews`
  ADD CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`),
  ADD CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`reviewer_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `reviews_ibfk_3` FOREIGN KEY (`reviewee_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `seller_categories`
--
ALTER TABLE `seller_categories`
  ADD CONSTRAINT `seller_categories_ibfk_1` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `seller_categories_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `seller_daily_usage`
--
ALTER TABLE `seller_daily_usage`
  ADD CONSTRAINT `seller_daily_usage_ibfk_1` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `seller_locations`
--
ALTER TABLE `seller_locations`
  ADD CONSTRAINT `seller_locations_ibfk_1` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `seller_profiles`
--
ALTER TABLE `seller_profiles`
  ADD CONSTRAINT `seller_profiles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `seller_skills`
--
ALTER TABLE `seller_skills`
  ADD CONSTRAINT `seller_skills_ibfk_1` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `seller_subscriptions`
--
ALTER TABLE `seller_subscriptions`
  ADD CONSTRAINT `seller_subscriptions_ibfk_1` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `seller_subscriptions_ibfk_2` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`);
COMMIT;

