| 1 | using System;
|
|---|
| 2 | using Microsoft.EntityFrameworkCore.Migrations;
|
|---|
| 3 | using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;
|
|---|
| 4 |
|
|---|
| 5 | #nullable disable
|
|---|
| 6 |
|
|---|
| 7 | namespace StockMaster.Migrations
|
|---|
| 8 | {
|
|---|
| 9 | /// <inheritdoc />
|
|---|
| 10 | public partial class InitialSetup : Migration
|
|---|
| 11 | {
|
|---|
| 12 | /// <inheritdoc />
|
|---|
| 13 | protected override void Up(MigrationBuilder migrationBuilder)
|
|---|
| 14 | {
|
|---|
| 15 | migrationBuilder.EnsureSchema(
|
|---|
| 16 | name: "stock_management");
|
|---|
| 17 |
|
|---|
| 18 | migrationBuilder.CreateTable(
|
|---|
| 19 | name: "category",
|
|---|
| 20 | schema: "stock_management",
|
|---|
| 21 | columns: table => new
|
|---|
| 22 | {
|
|---|
| 23 | category_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 24 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 25 | name = table.Column<string>(type: "character varying(50)", maxLength: 50, nullable: false),
|
|---|
| 26 | description = table.Column<string>(type: "text", nullable: false),
|
|---|
| 27 | created_at = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 28 | },
|
|---|
| 29 | constraints: table =>
|
|---|
| 30 | {
|
|---|
| 31 | table.PrimaryKey("PK_category", x => x.category_id);
|
|---|
| 32 | });
|
|---|
| 33 |
|
|---|
| 34 | migrationBuilder.CreateTable(
|
|---|
| 35 | name: "customer",
|
|---|
| 36 | schema: "stock_management",
|
|---|
| 37 | columns: table => new
|
|---|
| 38 | {
|
|---|
| 39 | customer_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 40 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 41 | name = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 42 | email = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 43 | phone = table.Column<string>(type: "character varying(20)", maxLength: 20, nullable: false),
|
|---|
| 44 | address = table.Column<string>(type: "text", nullable: false),
|
|---|
| 45 | created_at = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 46 | },
|
|---|
| 47 | constraints: table =>
|
|---|
| 48 | {
|
|---|
| 49 | table.PrimaryKey("PK_customer", x => x.customer_id);
|
|---|
| 50 | });
|
|---|
| 51 |
|
|---|
| 52 | migrationBuilder.CreateTable(
|
|---|
| 53 | name: "product_price_log",
|
|---|
| 54 | schema: "stock_management",
|
|---|
| 55 | columns: table => new
|
|---|
| 56 | {
|
|---|
| 57 | log_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 58 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 59 | product_id = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 60 | product_name = table.Column<string>(type: "text", nullable: false),
|
|---|
| 61 | old_price = table.Column<decimal>(type: "numeric", nullable: false),
|
|---|
| 62 | new_price = table.Column<decimal>(type: "numeric", nullable: false),
|
|---|
| 63 | changed_by = table.Column<string>(type: "text", nullable: false),
|
|---|
| 64 | changed_at = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 65 | },
|
|---|
| 66 | constraints: table =>
|
|---|
| 67 | {
|
|---|
| 68 | table.PrimaryKey("PK_product_price_log", x => x.log_id);
|
|---|
| 69 | });
|
|---|
| 70 |
|
|---|
| 71 | migrationBuilder.CreateTable(
|
|---|
| 72 | name: "supplier",
|
|---|
| 73 | schema: "stock_management",
|
|---|
| 74 | columns: table => new
|
|---|
| 75 | {
|
|---|
| 76 | supplier_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 77 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 78 | name = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 79 | contact_person = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 80 | phone = table.Column<string>(type: "character varying(20)", maxLength: 20, nullable: false),
|
|---|
| 81 | email = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 82 | address = table.Column<string>(type: "text", nullable: false),
|
|---|
| 83 | created_at = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 84 | },
|
|---|
| 85 | constraints: table =>
|
|---|
| 86 | {
|
|---|
| 87 | table.PrimaryKey("PK_supplier", x => x.supplier_id);
|
|---|
| 88 | });
|
|---|
| 89 |
|
|---|
| 90 | migrationBuilder.CreateTable(
|
|---|
| 91 | name: "users",
|
|---|
| 92 | schema: "stock_management",
|
|---|
| 93 | columns: table => new
|
|---|
| 94 | {
|
|---|
| 95 | user_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 96 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 97 | username = table.Column<string>(type: "character varying(50)", maxLength: 50, nullable: false),
|
|---|
| 98 | password = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false),
|
|---|
| 99 | full_name = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 100 | email = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 101 | role = table.Column<string>(type: "character varying(20)", maxLength: 20, nullable: false),
|
|---|
| 102 | is_active = table.Column<bool>(type: "boolean", nullable: false),
|
|---|
| 103 | created_at = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 104 | },
|
|---|
| 105 | constraints: table =>
|
|---|
| 106 | {
|
|---|
| 107 | table.PrimaryKey("PK_users", x => x.user_id);
|
|---|
| 108 | });
|
|---|
| 109 |
|
|---|
| 110 | migrationBuilder.CreateTable(
|
|---|
| 111 | name: "warehouse",
|
|---|
| 112 | schema: "stock_management",
|
|---|
| 113 | columns: table => new
|
|---|
| 114 | {
|
|---|
| 115 | warehouse_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 116 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 117 | name = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 118 | location = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false),
|
|---|
| 119 | capacity = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 120 | created_at = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 121 | },
|
|---|
| 122 | constraints: table =>
|
|---|
| 123 | {
|
|---|
| 124 | table.PrimaryKey("PK_warehouse", x => x.warehouse_id);
|
|---|
| 125 | });
|
|---|
| 126 |
|
|---|
| 127 | migrationBuilder.CreateTable(
|
|---|
| 128 | name: "product",
|
|---|
| 129 | schema: "stock_management",
|
|---|
| 130 | columns: table => new
|
|---|
| 131 | {
|
|---|
| 132 | product_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 133 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 134 | name = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
|
|---|
| 135 | description = table.Column<string>(type: "text", nullable: false),
|
|---|
| 136 | sku = table.Column<string>(type: "character varying(50)", maxLength: 50, nullable: false),
|
|---|
| 137 | unit_price = table.Column<decimal>(type: "numeric(12,2)", precision: 12, scale: 2, nullable: false),
|
|---|
| 138 | reorder_level = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 139 | category_id = table.Column<int>(type: "integer", nullable: true),
|
|---|
| 140 | supplier_id = table.Column<int>(type: "integer", nullable: true),
|
|---|
| 141 | is_active = table.Column<bool>(type: "boolean", nullable: false),
|
|---|
| 142 | created_at = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 143 | },
|
|---|
| 144 | constraints: table =>
|
|---|
| 145 | {
|
|---|
| 146 | table.PrimaryKey("PK_product", x => x.product_id);
|
|---|
| 147 | table.ForeignKey(
|
|---|
| 148 | name: "FK_product_category_category_id",
|
|---|
| 149 | column: x => x.category_id,
|
|---|
| 150 | principalSchema: "stock_management",
|
|---|
| 151 | principalTable: "category",
|
|---|
| 152 | principalColumn: "category_id");
|
|---|
| 153 | table.ForeignKey(
|
|---|
| 154 | name: "FK_product_supplier_supplier_id",
|
|---|
| 155 | column: x => x.supplier_id,
|
|---|
| 156 | principalSchema: "stock_management",
|
|---|
| 157 | principalTable: "supplier",
|
|---|
| 158 | principalColumn: "supplier_id");
|
|---|
| 159 | });
|
|---|
| 160 |
|
|---|
| 161 | migrationBuilder.CreateTable(
|
|---|
| 162 | name: "purchase_order",
|
|---|
| 163 | schema: "stock_management",
|
|---|
| 164 | columns: table => new
|
|---|
| 165 | {
|
|---|
| 166 | po_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 167 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 168 | order_date = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
|
|---|
| 169 | expected_delivery_date = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
|
|---|
| 170 | actual_delivery_date = table.Column<DateTime>(type: "timestamp without time zone", nullable: true),
|
|---|
| 171 | status = table.Column<string>(type: "character varying(20)", maxLength: 20, nullable: false),
|
|---|
| 172 | supplier_id = table.Column<int>(type: "integer", nullable: true),
|
|---|
| 173 | warehouse_id = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 174 | created_at = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 175 | },
|
|---|
| 176 | constraints: table =>
|
|---|
| 177 | {
|
|---|
| 178 | table.PrimaryKey("PK_purchase_order", x => x.po_id);
|
|---|
| 179 | table.ForeignKey(
|
|---|
| 180 | name: "FK_purchase_order_supplier_supplier_id",
|
|---|
| 181 | column: x => x.supplier_id,
|
|---|
| 182 | principalSchema: "stock_management",
|
|---|
| 183 | principalTable: "supplier",
|
|---|
| 184 | principalColumn: "supplier_id");
|
|---|
| 185 | table.ForeignKey(
|
|---|
| 186 | name: "FK_purchase_order_warehouse_warehouse_id",
|
|---|
| 187 | column: x => x.warehouse_id,
|
|---|
| 188 | principalSchema: "stock_management",
|
|---|
| 189 | principalTable: "warehouse",
|
|---|
| 190 | principalColumn: "warehouse_id",
|
|---|
| 191 | onDelete: ReferentialAction.Cascade);
|
|---|
| 192 | });
|
|---|
| 193 |
|
|---|
| 194 | migrationBuilder.CreateTable(
|
|---|
| 195 | name: "sale",
|
|---|
| 196 | schema: "stock_management",
|
|---|
| 197 | columns: table => new
|
|---|
| 198 | {
|
|---|
| 199 | sale_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 200 | .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
|
|---|
| 201 | date_time = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
|
|---|
| 202 | total_amount = table.Column<decimal>(type: "numeric(15,2)", precision: 15, scale: 2, nullable: false),
|
|---|
| 203 | user_id = table.Column<int>(type: "integer", nullable: true),
|
|---|
| 204 | customer_id = table.Column<int>(type: "integer", nullable: true),
|
|---|
| 205 | warehouse_id = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 206 | },
|
|---|
| 207 | constraints: table =>
|
|---|
| 208 | {
|
|---|
| 209 | table.PrimaryKey("PK_sale", x => x.sale_id);
|
|---|
| 210 | table.ForeignKey(
|
|---|
| 211 | name: "FK_sale_customer_customer_id",
|
|---|
| 212 | column: x => x.customer_id,
|
|---|
| 213 | principalSchema: "stock_management",
|
|---|
| 214 | principalTable: "customer",
|
|---|
| 215 | principalColumn: "customer_id");
|
|---|
| 216 | table.ForeignKey(
|
|---|
| 217 | name: "FK_sale_users_user_id",
|
|---|
| 218 | column: x => x.user_id,
|
|---|
| 219 | principalSchema: "stock_management",
|
|---|
| 220 | principalTable: "users",
|
|---|
| 221 | principalColumn: "user_id");
|
|---|
| 222 | table.ForeignKey(
|
|---|
| 223 | name: "FK_sale_warehouse_warehouse_id",
|
|---|
| 224 | column: x => x.warehouse_id,
|
|---|
| 225 | principalSchema: "stock_management",
|
|---|
| 226 | principalTable: "warehouse",
|
|---|
| 227 | principalColumn: "warehouse_id",
|
|---|
| 228 | onDelete: ReferentialAction.Cascade);
|
|---|
| 229 | });
|
|---|
| 230 |
|
|---|
| 231 | migrationBuilder.CreateTable(
|
|---|
| 232 | name: "warehouse_stock",
|
|---|
| 233 | schema: "stock_management",
|
|---|
| 234 | columns: table => new
|
|---|
| 235 | {
|
|---|
| 236 | warehouse_id = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 237 | product_id = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 238 | quantity_on_hand = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 239 | last_updated = table.Column<DateTime>(type: "timestamp without time zone", nullable: false)
|
|---|
| 240 | },
|
|---|
| 241 | constraints: table =>
|
|---|
| 242 | {
|
|---|
| 243 | table.PrimaryKey("PK_warehouse_stock", x => new { x.warehouse_id, x.product_id });
|
|---|
| 244 | table.ForeignKey(
|
|---|
| 245 | name: "FK_warehouse_stock_product_product_id",
|
|---|
| 246 | column: x => x.product_id,
|
|---|
| 247 | principalSchema: "stock_management",
|
|---|
| 248 | principalTable: "product",
|
|---|
| 249 | principalColumn: "product_id",
|
|---|
| 250 | onDelete: ReferentialAction.Cascade);
|
|---|
| 251 | table.ForeignKey(
|
|---|
| 252 | name: "FK_warehouse_stock_warehouse_warehouse_id",
|
|---|
| 253 | column: x => x.warehouse_id,
|
|---|
| 254 | principalSchema: "stock_management",
|
|---|
| 255 | principalTable: "warehouse",
|
|---|
| 256 | principalColumn: "warehouse_id",
|
|---|
| 257 | onDelete: ReferentialAction.Cascade);
|
|---|
| 258 | });
|
|---|
| 259 |
|
|---|
| 260 | migrationBuilder.CreateTable(
|
|---|
| 261 | name: "purchase_order_item",
|
|---|
| 262 | schema: "stock_management",
|
|---|
| 263 | columns: table => new
|
|---|
| 264 | {
|
|---|
| 265 | po_id = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 266 | product_id = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 267 | quantity = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 268 | unit_cost = table.Column<decimal>(type: "numeric(12,2)", precision: 12, scale: 2, nullable: false),
|
|---|
| 269 | received_quantity = table.Column<int>(type: "integer", nullable: false)
|
|---|
| 270 | },
|
|---|
| 271 | constraints: table =>
|
|---|
| 272 | {
|
|---|
| 273 | table.PrimaryKey("PK_purchase_order_item", x => new { x.po_id, x.product_id });
|
|---|
| 274 | table.ForeignKey(
|
|---|
| 275 | name: "FK_purchase_order_item_product_product_id",
|
|---|
| 276 | column: x => x.product_id,
|
|---|
| 277 | principalSchema: "stock_management",
|
|---|
| 278 | principalTable: "product",
|
|---|
| 279 | principalColumn: "product_id",
|
|---|
| 280 | onDelete: ReferentialAction.Cascade);
|
|---|
| 281 | table.ForeignKey(
|
|---|
| 282 | name: "FK_purchase_order_item_purchase_order_po_id",
|
|---|
| 283 | column: x => x.po_id,
|
|---|
| 284 | principalSchema: "stock_management",
|
|---|
| 285 | principalTable: "purchase_order",
|
|---|
| 286 | principalColumn: "po_id",
|
|---|
| 287 | onDelete: ReferentialAction.Cascade);
|
|---|
| 288 | });
|
|---|
| 289 |
|
|---|
| 290 | migrationBuilder.CreateTable(
|
|---|
| 291 | name: "sale_item",
|
|---|
| 292 | schema: "stock_management",
|
|---|
| 293 | columns: table => new
|
|---|
| 294 | {
|
|---|
| 295 | sale_id = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 296 | product_id = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 297 | quantity = table.Column<int>(type: "integer", nullable: false),
|
|---|
| 298 | unit_price_at_sale = table.Column<decimal>(type: "numeric(12,2)", precision: 12, scale: 2, nullable: false)
|
|---|
| 299 | },
|
|---|
| 300 | constraints: table =>
|
|---|
| 301 | {
|
|---|
| 302 | table.PrimaryKey("PK_sale_item", x => new { x.sale_id, x.product_id });
|
|---|
| 303 | table.ForeignKey(
|
|---|
| 304 | name: "FK_sale_item_product_product_id",
|
|---|
| 305 | column: x => x.product_id,
|
|---|
| 306 | principalSchema: "stock_management",
|
|---|
| 307 | principalTable: "product",
|
|---|
| 308 | principalColumn: "product_id",
|
|---|
| 309 | onDelete: ReferentialAction.Cascade);
|
|---|
| 310 | table.ForeignKey(
|
|---|
| 311 | name: "FK_sale_item_sale_sale_id",
|
|---|
| 312 | column: x => x.sale_id,
|
|---|
| 313 | principalSchema: "stock_management",
|
|---|
| 314 | principalTable: "sale",
|
|---|
| 315 | principalColumn: "sale_id",
|
|---|
| 316 | onDelete: ReferentialAction.Cascade);
|
|---|
| 317 | });
|
|---|
| 318 |
|
|---|
| 319 | migrationBuilder.CreateIndex(
|
|---|
| 320 | name: "IX_product_category_id",
|
|---|
| 321 | schema: "stock_management",
|
|---|
| 322 | table: "product",
|
|---|
| 323 | column: "category_id");
|
|---|
| 324 |
|
|---|
| 325 | migrationBuilder.CreateIndex(
|
|---|
| 326 | name: "IX_product_supplier_id",
|
|---|
| 327 | schema: "stock_management",
|
|---|
| 328 | table: "product",
|
|---|
| 329 | column: "supplier_id");
|
|---|
| 330 |
|
|---|
| 331 | migrationBuilder.CreateIndex(
|
|---|
| 332 | name: "IX_purchase_order_supplier_id",
|
|---|
| 333 | schema: "stock_management",
|
|---|
| 334 | table: "purchase_order",
|
|---|
| 335 | column: "supplier_id");
|
|---|
| 336 |
|
|---|
| 337 | migrationBuilder.CreateIndex(
|
|---|
| 338 | name: "IX_purchase_order_warehouse_id",
|
|---|
| 339 | schema: "stock_management",
|
|---|
| 340 | table: "purchase_order",
|
|---|
| 341 | column: "warehouse_id");
|
|---|
| 342 |
|
|---|
| 343 | migrationBuilder.CreateIndex(
|
|---|
| 344 | name: "IX_purchase_order_item_product_id",
|
|---|
| 345 | schema: "stock_management",
|
|---|
| 346 | table: "purchase_order_item",
|
|---|
| 347 | column: "product_id");
|
|---|
| 348 |
|
|---|
| 349 | migrationBuilder.CreateIndex(
|
|---|
| 350 | name: "IX_sale_customer_id",
|
|---|
| 351 | schema: "stock_management",
|
|---|
| 352 | table: "sale",
|
|---|
| 353 | column: "customer_id");
|
|---|
| 354 |
|
|---|
| 355 | migrationBuilder.CreateIndex(
|
|---|
| 356 | name: "IX_sale_user_id",
|
|---|
| 357 | schema: "stock_management",
|
|---|
| 358 | table: "sale",
|
|---|
| 359 | column: "user_id");
|
|---|
| 360 |
|
|---|
| 361 | migrationBuilder.CreateIndex(
|
|---|
| 362 | name: "IX_sale_warehouse_id",
|
|---|
| 363 | schema: "stock_management",
|
|---|
| 364 | table: "sale",
|
|---|
| 365 | column: "warehouse_id");
|
|---|
| 366 |
|
|---|
| 367 | migrationBuilder.CreateIndex(
|
|---|
| 368 | name: "IX_sale_item_product_id",
|
|---|
| 369 | schema: "stock_management",
|
|---|
| 370 | table: "sale_item",
|
|---|
| 371 | column: "product_id");
|
|---|
| 372 |
|
|---|
| 373 | migrationBuilder.CreateIndex(
|
|---|
| 374 | name: "IX_warehouse_stock_product_id",
|
|---|
| 375 | schema: "stock_management",
|
|---|
| 376 | table: "warehouse_stock",
|
|---|
| 377 | column: "product_id");
|
|---|
| 378 |
|
|---|
| 379 | // TABLOLAR OLUŞTUKTAN SONRA TRIGGER VE VIEWLARI OLUŞTURUYORUZ
|
|---|
| 380 | migrationBuilder.Sql(@"
|
|---|
| 381 | SET search_path TO stock_management, public;
|
|---|
| 382 |
|
|---|
| 383 | -- LOG TABLOSU
|
|---|
| 384 | CREATE TABLE IF NOT EXISTS product_price_log (
|
|---|
| 385 | log_id SERIAL PRIMARY KEY,
|
|---|
| 386 | product_id INT NOT NULL,
|
|---|
| 387 | product_name VARCHAR(100),
|
|---|
| 388 | old_price DECIMAL(12,2),
|
|---|
| 389 | new_price DECIMAL(12,2),
|
|---|
| 390 | changed_by VARCHAR(50),
|
|---|
| 391 | changed_at TIMESTAMP DEFAULT NOW()
|
|---|
| 392 | );
|
|---|
| 393 |
|
|---|
| 394 | -- FONKSIYONLAR VE TRIGGERLAR
|
|---|
| 395 | CREATE OR REPLACE FUNCTION trg_fn_recalculate_sale_total() RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
|---|
| 396 | DECLARE v_sale_id INT;
|
|---|
| 397 | BEGIN
|
|---|
| 398 | IF TG_OP = 'DELETE' THEN v_sale_id := OLD.sale_id; ELSE v_sale_id := NEW.sale_id; END IF;
|
|---|
| 399 | UPDATE sale SET total_amount = (SELECT COALESCE(SUM(quantity * unit_price_at_sale), 0) FROM sale_item WHERE sale_id = v_sale_id) WHERE sale_id = v_sale_id;
|
|---|
| 400 | RETURN NULL;
|
|---|
| 401 | END; $$;
|
|---|
| 402 |
|
|---|
| 403 | CREATE OR REPLACE TRIGGER trg_recalculate_sale_total AFTER INSERT OR UPDATE OR DELETE ON sale_item FOR EACH ROW EXECUTE FUNCTION trg_fn_recalculate_sale_total();
|
|---|
| 404 |
|
|---|
| 405 | CREATE OR REPLACE FUNCTION trg_fn_prevent_negative_stock() RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
|---|
| 406 | BEGIN
|
|---|
| 407 | IF NEW.quantity_on_hand < 0 THEN RAISE EXCEPTION 'Stock cannot be negative!'; END IF;
|
|---|
| 408 | RETURN NEW;
|
|---|
| 409 | END; $$;
|
|---|
| 410 |
|
|---|
| 411 | CREATE OR REPLACE TRIGGER trg_prevent_negative_stock BEFORE INSERT OR UPDATE ON warehouse_stock FOR EACH ROW EXECUTE FUNCTION trg_fn_prevent_negative_stock();
|
|---|
| 412 |
|
|---|
| 413 | CREATE OR REPLACE FUNCTION prevent_self_delete() RETURNS TRIGGER AS $$
|
|---|
| 414 | BEGIN
|
|---|
| 415 | IF OLD.username = current_setting('app.current_user', true) THEN RAISE EXCEPTION 'You cannot delete your own account.'; END IF;
|
|---|
| 416 | RETURN OLD;
|
|---|
| 417 | END; $$ LANGUAGE plpgsql;
|
|---|
| 418 |
|
|---|
| 419 | CREATE OR REPLACE TRIGGER trg_prevent_self_delete BEFORE DELETE ON users FOR EACH ROW EXECUTE FUNCTION prevent_self_delete();
|
|---|
| 420 |
|
|---|
| 421 | CREATE OR REPLACE FUNCTION log_price_change() RETURNS TRIGGER AS $$
|
|---|
| 422 | BEGIN
|
|---|
| 423 | IF NEW.unit_price <> OLD.unit_price THEN
|
|---|
| 424 | INSERT INTO product_price_log (product_id, product_name, old_price, new_price, changed_by)
|
|---|
| 425 | VALUES (OLD.product_id, OLD.name, OLD.unit_price, NEW.unit_price, current_setting('app.current_user', true));
|
|---|
| 426 | END IF;
|
|---|
| 427 | RETURN NEW;
|
|---|
| 428 | END; $$ LANGUAGE plpgsql;
|
|---|
| 429 |
|
|---|
| 430 | CREATE OR REPLACE TRIGGER trg_price_change BEFORE UPDATE ON product FOR EACH ROW EXECUTE FUNCTION log_price_change();
|
|---|
| 431 |
|
|---|
| 432 | -- VIEWS
|
|---|
| 433 | CREATE OR REPLACE VIEW vw_sales_by_day_of_week AS
|
|---|
| 434 | SELECT EXTRACT(ISODOW FROM s.date_time)::INT AS day_number,
|
|---|
| 435 | CASE EXTRACT(ISODOW FROM s.date_time)::INT WHEN 1 THEN 'Monday' WHEN 2 THEN 'Tuesday' WHEN 3 THEN 'Wednesday' WHEN 4 THEN 'Thursday' WHEN 5 THEN 'Friday' WHEN 6 THEN 'Saturday' WHEN 7 THEN 'Sunday' END AS day_name,
|
|---|
| 436 | COUNT(DISTINCT s.sale_id) AS total_sales, COALESCE(SUM(s.total_amount), 0) AS total_revenue, ROUND(COALESCE(AVG(s.total_amount), 0), 2) AS avg_sale_value, COALESCE(SUM(si.quantity), 0) AS total_items_sold
|
|---|
| 437 | FROM sale s JOIN sale_item si ON s.sale_id = si.sale_id GROUP BY EXTRACT(ISODOW FROM s.date_time) ORDER BY day_number;
|
|---|
| 438 |
|
|---|
| 439 | CREATE OR REPLACE VIEW vw_employee_sales_ranking AS
|
|---|
| 440 | SELECT u.user_id, u.full_name, u.role, COUNT(DISTINCT s.sale_id) AS total_sales, COALESCE(SUM(s.total_amount), 0) AS total_revenue, ROUND(COALESCE(AVG(s.total_amount), 0), 2) AS avg_sale_value, COUNT(DISTINCT s.customer_id) AS unique_customers, RANK() OVER (ORDER BY COALESCE(SUM(s.total_amount), 0) DESC) AS revenue_rank
|
|---|
| 441 | FROM users u LEFT JOIN sale s ON u.user_id = s.user_id GROUP BY u.user_id, u.full_name, u.role HAVING COUNT(s.sale_id) > 0;
|
|---|
| 442 |
|
|---|
| 443 | CREATE OR REPLACE VIEW vw_todays_sales_summary AS
|
|---|
| 444 | SELECT COUNT(DISTINCT s.sale_id) AS total_transactions_today, COALESCE(SUM(s.total_amount), 0) AS total_revenue_today, COALESCE(SUM(si.quantity), 0) AS total_items_sold_today, COUNT(DISTINCT s.customer_id) AS unique_customers_today, COUNT(DISTINCT s.warehouse_id) AS active_warehouses_today
|
|---|
| 445 | FROM sale s JOIN sale_item si ON s.sale_id = si.sale_id WHERE s.date_time::DATE = CURRENT_DATE;
|
|---|
| 446 | ");
|
|---|
| 447 | }
|
|---|
| 448 |
|
|---|
| 449 | /// <inheritdoc />
|
|---|
| 450 | protected override void Down(MigrationBuilder migrationBuilder)
|
|---|
| 451 | {
|
|---|
| 452 | migrationBuilder.DropTable(
|
|---|
| 453 | name: "product_price_log",
|
|---|
| 454 | schema: "stock_management");
|
|---|
| 455 |
|
|---|
| 456 | migrationBuilder.DropTable(
|
|---|
| 457 | name: "purchase_order_item",
|
|---|
| 458 | schema: "stock_management");
|
|---|
| 459 |
|
|---|
| 460 | migrationBuilder.DropTable(
|
|---|
| 461 | name: "sale_item",
|
|---|
| 462 | schema: "stock_management");
|
|---|
| 463 |
|
|---|
| 464 | migrationBuilder.DropTable(
|
|---|
| 465 | name: "warehouse_stock",
|
|---|
| 466 | schema: "stock_management");
|
|---|
| 467 |
|
|---|
| 468 | migrationBuilder.DropTable(
|
|---|
| 469 | name: "purchase_order",
|
|---|
| 470 | schema: "stock_management");
|
|---|
| 471 |
|
|---|
| 472 | migrationBuilder.DropTable(
|
|---|
| 473 | name: "sale",
|
|---|
| 474 | schema: "stock_management");
|
|---|
| 475 |
|
|---|
| 476 | migrationBuilder.DropTable(
|
|---|
| 477 | name: "product",
|
|---|
| 478 | schema: "stock_management");
|
|---|
| 479 |
|
|---|
| 480 | migrationBuilder.DropTable(
|
|---|
| 481 | name: "customer",
|
|---|
| 482 | schema: "stock_management");
|
|---|
| 483 |
|
|---|
| 484 | migrationBuilder.DropTable(
|
|---|
| 485 | name: "users",
|
|---|
| 486 | schema: "stock_management");
|
|---|
| 487 |
|
|---|
| 488 | migrationBuilder.DropTable(
|
|---|
| 489 | name: "warehouse",
|
|---|
| 490 | schema: "stock_management");
|
|---|
| 491 |
|
|---|
| 492 | migrationBuilder.DropTable(
|
|---|
| 493 | name: "category",
|
|---|
| 494 | schema: "stock_management");
|
|---|
| 495 |
|
|---|
| 496 | migrationBuilder.DropTable(
|
|---|
| 497 | name: "supplier",
|
|---|
| 498 | schema: "stock_management");
|
|---|
| 499 | }
|
|---|
| 500 | }
|
|---|
| 501 | }
|
|---|
