-- CreateTable
CREATE TABLE "roles" (
    "id" UUID NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "slug" VARCHAR(100) NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "roles_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "roles_slug_key" ON "roles"("slug");

CREATE TABLE "users" (
    "id" UUID NOT NULL,
    "role_id" UUID NOT NULL,
    "full_name" VARCHAR(255) NOT NULL,
    "initials" VARCHAR(10),
    "email" VARCHAR(255) NOT NULL,
    "phone" VARCHAR(50),
    "password_hash" TEXT NOT NULL,
    "avatar_url" TEXT,
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
ALTER TABLE "users" ADD CONSTRAINT "users_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "roles"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE TABLE "clients" (
    "id" UUID NOT NULL,
    "full_name" VARCHAR(255) NOT NULL,
    "phone" VARCHAR(50),
    "email" VARCHAR(255),
    "client_label" VARCHAR(50) NOT NULL,
    "client_status" VARCHAR(50) NOT NULL,
    "source" VARCHAR(100),
    "assigned_broker_id" UUID,
    "notes" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted_at" TIMESTAMP(3),
    CONSTRAINT "clients_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "clients_full_name_idx" ON "clients"("full_name");
CREATE INDEX "clients_phone_idx" ON "clients"("phone");
CREATE INDEX "clients_email_idx" ON "clients"("email");
CREATE INDEX "clients_client_label_idx" ON "clients"("client_label");
CREATE INDEX "clients_client_status_idx" ON "clients"("client_status");
CREATE INDEX "clients_assigned_broker_id_idx" ON "clients"("assigned_broker_id");
ALTER TABLE "clients" ADD CONSTRAINT "clients_assigned_broker_id_fkey" FOREIGN KEY ("assigned_broker_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE "properties" (
    "id" UUID NOT NULL,
    "owner_client_id" UUID NOT NULL,
    "assigned_broker_id" UUID,
    "deal_type" VARCHAR(50) NOT NULL,
    "property_type" VARCHAR(100) NOT NULL,
    "city" VARCHAR(100),
    "district" VARCHAR(150),
    "address" TEXT,
    "location_label" TEXT,
    "area" DECIMAL(10,2),
    "floor" INTEGER,
    "total_floors" INTEGER,
    "rooms" VARCHAR(50),
    "price" DECIMAL(12,2),
    "currency" VARCHAR(10) NOT NULL DEFAULT 'EUR',
    "rent_period" VARCHAR(20),
    "contract_type" VARCHAR(50),
    "pipeline_status" VARCHAR(100) NOT NULL,
    "latitude" DECIMAL(10,7),
    "longitude" DECIMAL(10,7),
    "description" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted_at" TIMESTAMP(3),
    CONSTRAINT "properties_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "properties_owner_client_id_idx" ON "properties"("owner_client_id");
CREATE INDEX "properties_assigned_broker_id_idx" ON "properties"("assigned_broker_id");
CREATE INDEX "properties_deal_type_idx" ON "properties"("deal_type");
CREATE INDEX "properties_property_type_idx" ON "properties"("property_type");
CREATE INDEX "properties_city_idx" ON "properties"("city");
CREATE INDEX "properties_district_idx" ON "properties"("district");
CREATE INDEX "properties_pipeline_status_idx" ON "properties"("pipeline_status");
CREATE INDEX "properties_latitude_longitude_idx" ON "properties"("latitude", "longitude");
ALTER TABLE "properties" ADD CONSTRAINT "properties_owner_client_id_fkey" FOREIGN KEY ("owner_client_id") REFERENCES "clients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "properties" ADD CONSTRAINT "properties_assigned_broker_id_fkey" FOREIGN KEY ("assigned_broker_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE "search_requests" (
    "id" UUID NOT NULL,
    "client_id" UUID NOT NULL,
    "assigned_broker_id" UUID,
    "deal_type" VARCHAR(50) NOT NULL,
    "property_type" VARCHAR(100),
    "preferred_locations" TEXT[],
    "location_text" TEXT,
    "min_area" DECIMAL(10,2),
    "max_area" DECIMAL(10,2),
    "preferred_area" DECIMAL(10,2),
    "preferred_floor" INTEGER,
    "min_price" DECIMAL(12,2),
    "max_price" DECIMAL(12,2),
    "target_price" DECIMAL(12,2),
    "currency" VARCHAR(10) NOT NULL DEFAULT 'EUR',
    "rent_period" VARCHAR(20),
    "rooms" VARCHAR(50),
    "contract_type" VARCHAR(50),
    "pipeline_status" VARCHAR(100) NOT NULL,
    "notes" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted_at" TIMESTAMP(3),
    CONSTRAINT "search_requests_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "search_requests_client_id_idx" ON "search_requests"("client_id");
CREATE INDEX "search_requests_assigned_broker_id_idx" ON "search_requests"("assigned_broker_id");
CREATE INDEX "search_requests_pipeline_status_idx" ON "search_requests"("pipeline_status");
ALTER TABLE "search_requests" ADD CONSTRAINT "search_requests_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "search_requests" ADD CONSTRAINT "search_requests_assigned_broker_id_fkey" FOREIGN KEY ("assigned_broker_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE "deals" (
    "id" UUID NOT NULL,
    "deal_type" VARCHAR(50) NOT NULL,
    "property_id" UUID,
    "search_request_id" UUID,
    "seller_client_id" UUID,
    "buyer_client_id" UUID,
    "landlord_client_id" UUID,
    "tenant_client_id" UUID,
    "assigned_broker_id" UUID,
    "stage" VARCHAR(100) NOT NULL,
    "final_price" DECIMAL(12,2),
    "currency" VARCHAR(10) NOT NULL DEFAULT 'EUR',
    "commission_amount" DECIMAL(12,2),
    "commission_currency" VARCHAR(10) NOT NULL DEFAULT 'EUR',
    "commission_paid" BOOLEAN NOT NULL DEFAULT false,
    "closed_at" TIMESTAMP(3),
    "notes" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted_at" TIMESTAMP(3),
    CONSTRAINT "deals_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "deals_stage_idx" ON "deals"("stage");
CREATE INDEX "deals_assigned_broker_id_idx" ON "deals"("assigned_broker_id");
CREATE INDEX "deals_closed_at_idx" ON "deals"("closed_at");
ALTER TABLE "deals" ADD CONSTRAINT "deals_property_id_fkey" FOREIGN KEY ("property_id") REFERENCES "properties"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "deals" ADD CONSTRAINT "deals_search_request_id_fkey" FOREIGN KEY ("search_request_id") REFERENCES "search_requests"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "deals" ADD CONSTRAINT "deals_seller_client_id_fkey" FOREIGN KEY ("seller_client_id") REFERENCES "clients"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "deals" ADD CONSTRAINT "deals_buyer_client_id_fkey" FOREIGN KEY ("buyer_client_id") REFERENCES "clients"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "deals" ADD CONSTRAINT "deals_landlord_client_id_fkey" FOREIGN KEY ("landlord_client_id") REFERENCES "clients"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "deals" ADD CONSTRAINT "deals_tenant_client_id_fkey" FOREIGN KEY ("tenant_client_id") REFERENCES "clients"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "deals" ADD CONSTRAINT "deals_assigned_broker_id_fkey" FOREIGN KEY ("assigned_broker_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE "rental_contracts" (
    "id" UUID NOT NULL,
    "deal_id" UUID,
    "property_id" UUID NOT NULL,
    "landlord_client_id" UUID,
    "tenant_client_id" UUID,
    "broker_id" UUID,
    "monthly_rent" DECIMAL(12,2) NOT NULL,
    "currency" VARCHAR(10) NOT NULL DEFAULT 'EUR',
    "start_date" DATE NOT NULL,
    "end_date" DATE NOT NULL,
    "status" VARCHAR(50) NOT NULL DEFAULT 'active',
    "renewed_from_id" UUID,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "rental_contracts_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "rental_contracts_end_date_idx" ON "rental_contracts"("end_date");
CREATE INDEX "rental_contracts_status_idx" ON "rental_contracts"("status");
ALTER TABLE "rental_contracts" ADD CONSTRAINT "rental_contracts_deal_id_fkey" FOREIGN KEY ("deal_id") REFERENCES "deals"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "rental_contracts" ADD CONSTRAINT "rental_contracts_property_id_fkey" FOREIGN KEY ("property_id") REFERENCES "properties"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "rental_contracts" ADD CONSTRAINT "rental_contracts_landlord_client_id_fkey" FOREIGN KEY ("landlord_client_id") REFERENCES "clients"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "rental_contracts" ADD CONSTRAINT "rental_contracts_tenant_client_id_fkey" FOREIGN KEY ("tenant_client_id") REFERENCES "clients"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "rental_contracts" ADD CONSTRAINT "rental_contracts_broker_id_fkey" FOREIGN KEY ("broker_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "rental_contracts" ADD CONSTRAINT "rental_contracts_renewed_from_id_fkey" FOREIGN KEY ("renewed_from_id") REFERENCES "rental_contracts"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE "rental_contract_actions" (
    "id" UUID NOT NULL,
    "rental_contract_id" UUID NOT NULL,
    "user_id" UUID,
    "action" VARCHAR(50) NOT NULL,
    "old_end_date" DATE,
    "new_end_date" DATE,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "rental_contract_actions_pkey" PRIMARY KEY ("id")
);

ALTER TABLE "rental_contract_actions" ADD CONSTRAINT "rental_contract_actions_rental_contract_id_fkey" FOREIGN KEY ("rental_contract_id") REFERENCES "rental_contracts"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "rental_contract_actions" ADD CONSTRAINT "rental_contract_actions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE "calendar_events" (
    "id" UUID NOT NULL,
    "title" VARCHAR(255) NOT NULL,
    "event_type" VARCHAR(100) NOT NULL,
    "description" TEXT,
    "start_datetime" TIMESTAMP(3) NOT NULL,
    "end_datetime" TIMESTAMP(3),
    "client_id" UUID,
    "property_id" UUID,
    "deal_id" UUID,
    "assigned_broker_id" UUID,
    "created_by" UUID,
    "status" VARCHAR(50) NOT NULL DEFAULT 'scheduled',
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "calendar_events_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "calendar_events_start_datetime_idx" ON "calendar_events"("start_datetime");
CREATE INDEX "calendar_events_event_type_idx" ON "calendar_events"("event_type");
CREATE INDEX "calendar_events_assigned_broker_id_idx" ON "calendar_events"("assigned_broker_id");
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_property_id_fkey" FOREIGN KEY ("property_id") REFERENCES "properties"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_deal_id_fkey" FOREIGN KEY ("deal_id") REFERENCES "deals"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_assigned_broker_id_fkey" FOREIGN KEY ("assigned_broker_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE "targets" (
    "id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "period_type" VARCHAR(20) NOT NULL,
    "period_start" DATE NOT NULL,
    "period_end" DATE NOT NULL,
    "target_amount" DECIMAL(12,2) NOT NULL,
    "currency" VARCHAR(10) NOT NULL DEFAULT 'EUR',
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "targets_pkey" PRIMARY KEY ("id")
);

ALTER TABLE "targets" ADD CONSTRAINT "targets_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE TABLE "audit_logs" (
    "id" UUID NOT NULL,
    "user_id" UUID,
    "entity_type" VARCHAR(100) NOT NULL,
    "entity_id" UUID NOT NULL,
    "action" VARCHAR(100) NOT NULL,
    "old_data" JSONB,
    "new_data" JSONB,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
);

ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;