-- Lotea · esquema completo para MySQL / MariaDB
--
-- Generado desde las migraciones de la aplicación. Impórtalo en phpMyAdmin
-- sobre una base vacía si prefieres no usar el instalador web; en ese caso
-- marca la instalación como hecha creando storage/app/installed.json.
--
-- Requiere MySQL 5.7+ o MariaDB 10.2+ por el uso de columnas JSON.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

create table `users` (`id` bigint unsigned not null auto_increment primary key, `name` varchar(255) not null, `email` varchar(255) not null, `email_verified_at` timestamp null, `password` varchar(255) not null, `remember_token` varchar(100) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `users` add unique `users_email_unique`(`email`);

create table `password_reset_tokens` (`email` varchar(255) not null, `token` varchar(255) not null, `created_at` timestamp null, primary key (`email`)) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

create table `sessions` (`id` varchar(255) not null, `user_id` bigint unsigned null, `ip_address` varchar(45) null, `user_agent` text null, `payload` longtext not null, `last_activity` int not null, primary key (`id`)) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `sessions` add index `sessions_user_id_index`(`user_id`);

alter table `sessions` add index `sessions_last_activity_index`(`last_activity`);

create table `cache` (`key` varchar(255) not null, `value` mediumtext not null, `expiration` bigint not null, primary key (`key`)) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `cache` add index `cache_expiration_index`(`expiration`);

create table `cache_locks` (`key` varchar(255) not null, `owner` varchar(255) not null, `expiration` bigint not null, primary key (`key`)) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `cache_locks` add index `cache_locks_expiration_index`(`expiration`);

create table `jobs` (`id` bigint unsigned not null auto_increment primary key, `queue` varchar(255) not null, `payload` longtext not null, `attempts` smallint unsigned not null, `reserved_at` int unsigned null, `available_at` int unsigned not null, `created_at` int unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `jobs` add index `jobs_queue_index`(`queue`);

create table `job_batches` (`id` varchar(255) not null, `name` varchar(255) not null, `total_jobs` int not null, `pending_jobs` int not null, `failed_jobs` int not null, `failed_job_ids` longtext not null, `options` mediumtext null, `cancelled_at` int null, `created_at` int not null, `finished_at` int null, primary key (`id`)) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

create table `failed_jobs` (`id` bigint unsigned not null auto_increment primary key, `uuid` varchar(255) not null, `connection` varchar(255) not null, `queue` varchar(255) not null, `payload` longtext not null, `exception` longtext not null, `failed_at` timestamp not null default CURRENT_TIMESTAMP) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `failed_jobs` add index `failed_jobs_connection_queue_failed_at_index`(`connection`, `queue`, `failed_at`);

alter table `failed_jobs` add unique `failed_jobs_uuid_unique`(`uuid`);

create table `passkeys` (`id` bigint unsigned not null auto_increment primary key, `user_id` bigint unsigned not null, `name` varchar(255) not null, `credential_id` varchar(255) not null, `credential` json not null, `last_used_at` timestamp null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `passkeys` add constraint `passkeys_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade;

alter table `passkeys` add index `passkeys_user_id_index`(`user_id`);

alter table `passkeys` add unique `passkeys_credential_id_unique`(`credential_id`);

alter table `users` add `two_factor_secret` text null after `password`;

alter table `users` add `two_factor_recovery_codes` text null after `two_factor_secret`;

alter table `users` add `two_factor_confirmed_at` timestamp null after `two_factor_recovery_codes`;

alter table `users` add `role` varchar(20) not null default 'client' after `email`;

create table `ai_integrations` (`id` bigint unsigned not null auto_increment primary key, `provider` varchar(255) not null, `api_key` text null, `model` varchar(255) not null default 'claude-sonnet-5', `enabled` tinyint(1) not null default '0', `last_tested_at` timestamp null, `last_status` varchar(255) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `ai_integrations` add unique `ai_integrations_provider_unique`(`provider`);

create table `developments` (`id` bigint unsigned not null auto_increment primary key, `name` varchar(255) not null, `slug` varchar(255) not null, `location` varchar(255) not null, `headline` varchar(255) null, `source_name` varchar(255) null, `source_type` varchar(255) null, `source_page` int unsigned null, `meters_per_grid` decimal(8, 3) not null default '3', `published` tinyint(1) not null default '0', `published_at` timestamp null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `developments` add unique `developments_slug_unique`(`slug`);

create table `lots` (`id` bigint unsigned not null auto_increment primary key, `development_id` bigint unsigned not null, `external_id` varchar(255) not null, `code` varchar(255) not null, `area` decimal(12, 2) not null, `price` decimal(14, 2) not null default '0', `status` varchar(255) not null default 'available', `points` json not null, `frontage` decimal(10, 2) not null default '0', `depth` decimal(10, 2) not null default '0', `orientation` varchar(255) not null default 'Por definir', `financing` varchar(255) not null default 'Por configurar', `detection_source` varchar(255) null, `detection_confidence` tinyint unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `lots` add constraint `lots_development_id_foreign` foreign key (`development_id`) references `developments` (`id`) on delete cascade;

alter table `lots` add unique `lots_development_id_code_unique`(`development_id`, `code`);

alter table `lots` add unique `lots_development_id_external_id_unique`(`development_id`, `external_id`);

alter table `lots` add index `lots_development_id_status_index`(`development_id`, `status`);

alter table `developments` add `blocks` json null after `meters_per_grid`;

alter table `lots` add `block` varchar(255) null after `code`;

alter table `lots` add `lot_number` varchar(255) null after `block`;

alter table `lots` add `measurements` json null after `depth`;

alter table `lots` add `status_evidence` text null after `status`;

alter table `lots` add `status_confidence` tinyint unsigned null after `status_evidence`;

alter table `developments` add `features` json null after `blocks`;

alter table `developments` add `source_image_path` varchar(255) null after `source_page`;

alter table `developments` add `plan_frame` json null after `features`;

alter table `developments` add `plan_report` json null after `plan_frame`;

alter table `developments` add `plan_legend` json null after `plan_report`;

alter table `developments` add `plan_layers` json null after `plan_legend`;

alter table `developments` add `plan_source` json null after `plan_layers`;

alter table `lots` add `label_point` json null after `points`;

alter table `lots` add `area_raw` varchar(255) null after `area`;

alter table `lots` add `digitization_pass` varchar(255) null after `detection_confidence`;

alter table `lots` add `needs_review` tinyint(1) not null default '0' after `digitization_pass`;

alter table `lots` add `note` text null after `needs_review`;

create table `prospects` (`id` bigint unsigned not null auto_increment primary key, `name` varchar(255) not null, `email` varchar(255) null, `phone` varchar(255) null, `source` varchar(255) not null default 'portal', `stage` varchar(255) not null default 'nuevo', `development_id` bigint unsigned null, `lot_id` bigint unsigned null, `budget` decimal(14, 2) null, `notes` text null, `last_contacted_at` timestamp null, `next_follow_up_at` timestamp null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `prospects` add constraint `prospects_development_id_foreign` foreign key (`development_id`) references `developments` (`id`) on delete set null;

alter table `prospects` add constraint `prospects_lot_id_foreign` foreign key (`lot_id`) references `lots` (`id`) on delete set null;

alter table `prospects` add index `prospects_stage_index`(`stage`);

alter table `prospects` add index `prospects_development_id_index`(`development_id`);

create table `prospect_interactions` (`id` bigint unsigned not null auto_increment primary key, `prospect_id` bigint unsigned not null, `user_id` bigint unsigned null, `channel` varchar(255) not null default 'nota', `stage_from` varchar(255) null, `stage_to` varchar(255) null, `summary` text not null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `prospect_interactions` add constraint `prospect_interactions_prospect_id_foreign` foreign key (`prospect_id`) references `prospects` (`id`) on delete cascade;

alter table `prospect_interactions` add constraint `prospect_interactions_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete set null;

alter table `prospect_interactions` add index `prospect_interactions_prospect_id_created_at_index`(`prospect_id`, `created_at`);

create table `project_media` (`id` bigint unsigned not null auto_increment primary key, `development_id` bigint unsigned not null, `kind` varchar(255) not null default 'image', `disk_path` varchar(255) null, `external_url` varchar(255) null, `title` varchar(255) null, `position` int unsigned not null default '0', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `project_media` add constraint `project_media_development_id_foreign` foreign key (`development_id`) references `developments` (`id`) on delete cascade;

alter table `project_media` add index `project_media_development_id_position_index`(`development_id`, `position`);

alter table `project_media` add `is_cover` tinyint(1) not null default '0' after `kind`;

alter table `developments` add `address` varchar(255) null after `location`;

alter table `developments` add `latitude` decimal(10, 7) null after `address`;

alter table `developments` add `longitude` decimal(10, 7) null after `latitude`;

alter table `lots` add `status_locked` tinyint(1) not null default '0' after `status`;

alter table `lots` add `missing_from_plan_at` timestamp null after `needs_review`;

alter table `developments` add `whatsapp` varchar(24) null after `longitude`;

alter table `developments` add `contact_email` varchar(255) null after `whatsapp`;

alter table `developments` add `down_payment_percent` tinyint unsigned null after `contact_email`;

alter table `developments` add `financing_months` smallint unsigned null after `down_payment_percent`;

alter table `lots` add `price_cents` bigint not null default '0' after `price`;

alter table `prospects` add `budget_cents` bigint null after `budget`;

alter table `lots` drop `price`;

alter table `prospects` drop `budget`;

create table `reservations` (`id` bigint unsigned not null auto_increment primary key, `development_id` bigint unsigned not null, `lot_id` bigint unsigned not null, `prospect_id` bigint unsigned null, `user_id` bigint unsigned null, `amount_cents` bigint not null default '0', `status` varchar(255) not null default 'active', `reserved_at` timestamp not null, `expires_at` timestamp not null, `closed_at` timestamp null, `notes` text null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci';

alter table `reservations` add constraint `reservations_development_id_foreign` foreign key (`development_id`) references `developments` (`id`) on delete cascade;

alter table `reservations` add constraint `reservations_lot_id_foreign` foreign key (`lot_id`) references `lots` (`id`) on delete cascade;

alter table `reservations` add constraint `reservations_prospect_id_foreign` foreign key (`prospect_id`) references `prospects` (`id`) on delete set null;

alter table `reservations` add constraint `reservations_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete set null;

alter table `reservations` add index `reservations_status_expires_at_index`(`status`, `expires_at`);

alter table `reservations` add index `reservations_development_id_status_index`(`development_id`, `status`);

alter table `lots` add `lot_sort` int unsigned not null default '0' after `lot_number`;

alter table `lots` add index `lots_ordering_index`(`development_id`, `block`, `lot_sort`);

SET FOREIGN_KEY_CHECKS = 1;

-- Deja registradas las migraciones para que `php artisan migrate` no las repita.

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('0001_01_01_000000_create_users_table', 1),
('0001_01_01_000001_create_cache_table', 1),
('0001_01_01_000002_create_jobs_table', 1),
('2024_01_01_000000_create_passkeys_table', 1),
('2025_08_14_170933_add_two_factor_columns_to_users_table', 1),
('2026_08_24_000000_add_role_to_users_table', 1),
('2026_08_24_010000_create_ai_integrations_table', 1),
('2026_08_24_020000_create_developments_and_lots_tables', 1),
('2026_08_24_030000_add_plan_reading_fields_to_lots_table', 1),
('2026_08_24_040000_add_claude_status_evidence_to_lots_table', 1),
('2026_08_24_050000_add_plan_features_to_developments_table', 1),
('2026_09_03_000000_add_source_image_path_to_developments_table', 1),
('2026_09_05_000000_add_masterplan_fields_to_plan_tables', 1),
('2026_09_05_010000_create_prospects_tables', 1),
('2026_09_05_020000_create_project_media_table', 1),
('2026_09_05_030000_add_cover_flag_to_project_media', 1),
('2026_09_05_040000_add_location_and_status_lock', 1),
('2026_09_05_050000_add_contact_and_financing_to_developments', 1),
('2026_09_05_060000_store_money_as_cents', 1),
('2026_09_05_070000_create_reservations_and_quotes', 1),
('2026_09_05_080000_add_lot_sort_to_lots', 1);